Generating a derived table in SSRS from last purchases in two different SQL Server tables, and then joining with a third

Question

Suppose I have three tables:

one of customer info with a unique ID (CUST_ID)
one of customer pants purchases 3 columns (CUST_ID, PANT_TYPE, PANT_DATE)
one of customer shirts purchases 3 columns (CUST_ID, SHIRT_TYPE, SHIRT_DATE)

Additionally, I wish to exclude certain pant types and certain shirt types (PANT_TYPE IS NOT 'JEANS', SHIRT_TYPE IS NOT 'TUXEDO'), and only grab the most recent pant purchase and shirt purchase.

So, ideally, I would end up with final table like this:

CUST_ID, LAST_PANT_TYPE, LAST_PANT_DATE, LAST_SHIRT_TYPE, LAST_SHIRT_DATE

For four hours at work I tried to get this solution to work, but the 'OVER' statement breaks SSRS and causes problems in testing the query and even drops the fields randomly at times. So, I am thinking derived tables of some sort might work.

I am pretty new to SQL and have learned a lot trying to solve this, but I need to get there STAT.

score 1 · Answer 1 · answered Oct 12 '18 at 23:19

You could try using the APPLY operator, e.g.:

SELECT
    C.CUST_ID
    , PANT.PANT_TYPE LAST_PANT_TYPE
    , PANT.PANT_DATE LAST_PANT_DATE
    , SHIRT.SHIRT_TYPE LAST_SHIRT_TYPE
    , SHIRT.SHIRT_DATE LAST_SHIRT_DATE
FROM
    CUSTOMER C
    OUTER APPLY
    (
        SELECT TOP 1
            P.PANT_TYPE
            , P.PANT_DATE
        FROM PANT P
        WHERE
            P.CUST_ID = C.CUST_ID
            AND P.PANT_TYPE <> 'JEANS'
        ORDER BY P.PANT_DATE DESC
    ) PANT
    OUTER APPLY
    (
        SELECT TOP 1
            S.SHIRT_TYPE
            , S.SHIRT_DATE
        FROM SHIRT S
        WHERE
            S.CUST_ID = C.CUST_ID
            AND S.SHIRT_TYPE <> 'TUXEDO'
        ORDER BY S.SHIRT_DATE DESC
    ) SHIRT

Using OUTER APPLY (as opposed to CROSS APPLY) ensures that none of the customers will be filtered out (NULL will be returned in the 'PANT' or 'SHIRT' columns if there is no data for the customer in their respective tables).

score 1 · Accepted Answer · answered Oct 13 '18 at 09:42

1

Using Common Table Expressions for your derived tables, you query could look like this:

WITH
  p (cust_id, pant_type, pant_date, rn) AS (
    SELECT cust_id, pant_type, pant_date,
      ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY pant_date DESC)
    FROM Pants WHERE pant_type NOT IN ('JEANS')
  ),
  s (cust_id, shirt_type, shirt_date, rn) AS (
    SELECT cust_id, shirt_type, shirt_date,
      ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY shirt_date DESC)
      FROM Shirts WHERE shirt_type NOT IN ('TUXEDO')
  )
SELECT c.cust_id, 
  p.pant_type AS last_pant_type, p.pant_date AS last_pant_date,
  s.shirt_type AS last_shirt_type, s.shirt_date AS last_shirt_date
FROM Customers c
  LEFT JOIN p ON c.cust_id = p.cust_id AND p.rn = 1
  LEFT JOIN s ON c.cust_id = s.cust_id AND s.rn = 1;

answered Oct 13 '18 at 09:42

Wolfgang Kais

4,010
2
10
17

i think you missed the part where I can't use OVER? – traggatmot Oct 13 '18 at 14:56
Yes, indeed. What database version are you using? "OVER" will not break anything, but of course you need at least SQL Server 2008. – Wolfgang Kais Oct 13 '18 at 16:40
OVER technically works, but it breaks the interface in SSRS's query designer, that I need to use to test out my query and see how the results shape up because I'm a noob. – traggatmot Oct 14 '18 at 20:50
@traggatmot - There are plenty other issues with the SSRS query designer - don't rely on it's limitations to determine what you can do in an SSRS query. – Hannover Fist Oct 15 '18 at 22:02

score 0 · Answer 3 · answered Oct 12 '18 at 23:21

I ended up making data and using ROW_NUMBER to identify the last purchase.

SELECT 1 AS CUST_ID
INTO #CUSTOMERS 
UNION 
SELECT 2
UNION 
SELECT 3
UNION 
SELECT 4

SELECT 1 AS CUST_ID, 'SLACKS' AS PANT_TYPE, CAST('1/1/2017' AS DATE) AS PANT_DATE
INTO #PANTS
UNION
SELECT 1, 'CARGO SHORTS', CAST('2/2/2018' AS DATE)
UNION
SELECT 1, 'SLACKS', CAST('3/3/2018' AS DATE)
UNION
SELECT 2, 'CARGO PANTS', CAST('2/2/2017' AS DATE)
UNION
SELECT 2, 'SLACKS', CAST('4/4/2018' AS DATE)
UNION
SELECT 3, 'CARGO PANTS', CAST('4/1/2018' AS DATE)
UNION
SELECT 3, 'SLACKS', CAST('5/8/2017' AS DATE)
UNION
SELECT 4, 'CARGO SHORTS', CAST('2/2/2018' AS DATE)
UNION
SELECT 4, 'SLACKS', CAST('3/3/2018' AS DATE)
UNION
SELECT 4, 'CARGO PANTS', CAST('2/2/2017' AS DATE)
UNION
SELECT 4, 'SLACKS', CAST('4/4/2018' AS DATE)
UNION
SELECT 4, 'CARGO PANTS', CAST('4/1/2018' AS DATE)
UNION
SELECT 4, 'SLACKS', CAST('5/8/2017' AS DATE)

SELECT 1 AS CUST_ID, 'POLO' AS SHIRT_TYPE, CAST('1/1/2017' AS DATE) AS SHIRT_DATE
INTO #SHIRTS
UNION
SELECT 1, 'POLO - LONG SLEEVE', CAST('2/2/2018' AS DATE)
UNION
SELECT 1, 'POLO - LONG SLEEVE', CAST('3/3/2018' AS DATE)
UNION
SELECT 2, 'POLO', CAST('2/2/2017' AS DATE)
UNION
SELECT 2, 'POLO - LONG SLEEVE', CAST('4/4/2018' AS DATE)
UNION
SELECT 3, 'T-SHIRT', CAST('4/1/2018' AS DATE)
UNION
SELECT 3, 'POLO', CAST('5/8/2017' AS DATE)
UNION
SELECT 4, 'T-SHIRT', CAST('2/2/2018' AS DATE)
UNION
SELECT 4, 'POLO', CAST('3/3/2018' AS DATE)
UNION
SELECT 4, 'T-SHIRT', CAST('2/2/2017' AS DATE)
UNION
SELECT 4, 'POLO - LONG SLEEVE', CAST('4/4/2018' AS DATE)
UNION
SELECT 4, 'T-SHIRT', CAST('4/1/2018' AS DATE)
UNION
SELECT 4, 'POLO', CAST('5/8/2017' AS DATE)

SELECT C.CUST_ID, P.PANT_TYPE, P.PANT_DATE, 
    S.SHIRT_TYPE, S.SHIRT_DATE
FROM #CUSTOMERS C
LEFT JOIN (SELECT CUST_ID, PANT_TYPE, PANT_DATE, ROW_NUMBER()OVER(PARTITION BY CUST_ID ORDER BY PANT_DATE DESC) AS ROW_NUM FROM #PANTS) P ON P.CUST_ID = C.CUST_ID AND P.ROW_NUM = 1 
LEFT JOIN (SELECT CUST_ID, SHIRT_TYPE, SHIRT_DATE, ROW_NUMBER()OVER(PARTITION BY CUST_ID ORDER BY SHIRT_DATE DESC) AS ROW_NUM FROM #SHIRTS) S ON S.CUST_ID = C.CUST_ID AND S.ROW_NUM = 1

Generating a derived table in SSRS from last purchases in two different SQL Server tables, and then joining with a third

3 Answers3