window functions to solve complex date manipulations

ppincus 0
Viewed 46

I have the following list of transactions:

transaction_id transaction_type spend_year transaction_date
1 New Business 0 2020-11-01
2 Supplemental 0 2021-08-23
3 Renewal 1 2021-10-15
4 Supplemental 1 2022-02-01
5 Supplemental 1 2022-05-22
6 Renewal 2 2022-07-15

Note: spend_year is used to group transactions together based on renewal periods.

With the table above, I need to calculate two additional fields:

(1) spend_year_start_date

when spend_year = 0, this should be the min(transaction_date) (this should be the first ever transaction date)

when spend_year > 0, this should be the dateadd('month', -3, (min(transaction_date) over(partition by spend_year))) i.e. 3 months preceding the renewal transaction date for that spend year

(2) spend_year_end_date

this should be the spend_year_start_date of the next spend_year group

The desired output table should look like this:

transaction_id transaction_type spend_year transaction_date spend_year_start_date spend_year_end_date
1 New Business 0 2020-11-01 2020-11-01 2021-07-15
2 Supplemental 0 2021-08-23 2020-11-01 2021-07-15
3 Renewal 1 2021-10-15 2021-07-15 2022-04-15
4 Supplemental 1 2022-02-01 2021-07-15 2022-04-15
5 Supplemental 1 2022-05-22 2021-07-15 2022-04-15
6 Renewal 2 2022-07-15 2022-04-15 null

I've been experimenting with various window functions, but still can't capture the logic correctly. Please help!

analysis date window-functions database sql
2 Answers

We can use the LEAD() analytic function here:

WITH cte AS (
    SELECT *, CASE WHEN spend_year = 0
                   THEN MIN(transaction_date) OVER (PARTITION BY spend_year)
                   ELSE DATEADD(month, -3, MIN(transaction_date) OVER
                            (PARTITION BY spend_year)) END AS spend_year_start_date
    FROM yourTable
),
cte2 AS (
    SELECT spend_year,
           LEAD(MIN(spend_year_start_date)) OVER (ORDER BY spend_year) AS spend_year_lead
    FROM cte
    GROUP BY spend_year
)

SELECT t1.transaction_id, t1.transaction_type, t1.spend_year, t1.transaction_date,
       t1.spend_year_start_date, t2.spend_year_lead AS spend_year_end_date
FROM cte t1
INNER JOIN cte2 t2 ON t2.spend_year = t1.spend_year
ORDER BY t1.transaction_id;
Tim Biegeleisen0

Supposing that (next spend_year= current spend_year+1), you may try the following:

SELECT T.transaction_id, T.transaction_type, T.spend_year, T.transaction_date,
       CASE 
           WHEN T.spend_year = 0
               THEN MIN(T.transaction_date) OVER (PARTITION BY T.spend_year)
               ELSE DATEADD(MONTH, -3, MIN(T.transaction_date) OVER (PARTITION BY T.spend_year)) 
       END AS spend_year_start_date,
       DATEADD(MONTH, -3, D.transaction_date) spend_year_end_date
FROM table_name T
LEFT JOIN
  (SELECT MIN(transaction_date) transaction_date, spend_year FROM table_name GROUP BY spend_year) D
ON T.spend_year+1 = D.spend_year
ORDER BY T.transaction_id

See a demo.

userGpjR0
Related