SQL joining tables based off latest previous date-CodePudding

Let's say I have two tables for example:

Table 1 - customer order information

x---------x--------x-------------x
 cust_id  |  item  |  order date |
x---------x--------x-------------x
   1      |  100   |  01/01/2020 |
   1      |  112   |  03/07/2022 |
   2      |  100   |  01/02/2020 |
   2      |  168   |  05/03/2022 |
   3      |  200   |  15/06/2021 |
----------x--------x-------------x

and Table 2 - customer membership status

x---------x--------x-------------x
  cust_id | Status | startdate   |
x---------x--------x-------------x
    1     | silver | 01/01/2019  |
    1     | bronze | 05/12/2019  |
    1     | gold   | 05/06/2022  |
    2     | silver | 24/12/2021  |
----------x--------x-------------x

I want to join the two tables so that I can see what their membership status was at the time of purchase, to produce something like this:

x---------x--------x-------------x----------x
 cust_id  |  item  |  order date |  status  |
x---------x--------x-------------x----------x
   1      |  100   |  01/01/2020 |  bronze  |
   1      |  112   |  03/07/2022 |  gold    |
   2      |  100   |  01/02/2020 |  NULL    |
   2      |  168   |  05/03/2022 |  silver  |
   3      |  200   |  15/06/2021 |  NULL    |
----------x--------x-------------x----------x

Tried multiple ways include min/max, >=, group by having etc with no luck. I feel like multiple joins are going to be needed here but I can't figure out - any help would be greatly appreciated. (also note: dates are in European/au not American format.)

CodePudding user response：

SELECT
    [cust_id],
    [item],
    [order date],
    [status]
FROM
(
    SELECT
        t1.[cust_id],
        t1.[item],
        t1.[order date],
        t2.[status],
        ROW_NUMBER() OVER (PARTITION BY t1.[cust_id], t1.[item] ORDER BY t2.[startdate] DESC) rn
    FROM #t1 t1
    LEFT JOIN #t2 t2
        ON t1.[cust_id] = t2.[cust_id] AND t1.[order date] >= t2.[startdate]
) a
WHERE rn = 1

CodePudding user response：

Try the following using LEAD function to define periods limits for each status:

SELECT T.cust_id, T.item, T.orderdate, D.status
FROM order_information T
LEFT JOIN
  (
    SELECT cust_id, Status, startdate,
           LEAD(startdate, 1, GETDATE()) OVER (PARTITION BY cust_id ORDER BY startdate) AS enddate
    FROM customer_membership
  ) D
ON T.cust_id = D.cust_id AND
   T.orderdate BETWEEN D.startdate AND D.enddate

See a demo on SQL Server.

CodePudding user response：

SELECT
    o.cust_id,
    o.item,
    o.order_date,
    m.status 
FROM
    customer_order o 
    LEFT JOIN
        customer_membership m 
        ON o.cust_id = m.cust_id 
        AND o.order_date > m.start_date 
GROUP BY
    o.cust_id,
    o.item,
    o.order_date 
HAVING
    Count(m.status) = 0 
    OR m.start_date = Max(m.start_date);