I have the following list of transactions:

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:

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

CodePudding user response：

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;

CodePudding user response：

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.