SQL First In First Out Loyalty

Question

developers and analysts. I have some experience with SQL and resort to similar messages. However, this is a little more than a niche. Thank you in advance for your help.

I have the following dataset (edited by Apology)

Customization

CREATE TABLE CustomerPoints ( CustomerID INT, [Date] Date, Points INT ) INSERT INTO CustomerPoints VALUES (1, '20150101', 500), (1, '20150201', -400), (1, '20151101', 300), (1, '20151201', -400) 

and you need to enable it (edited. The numbers in the previous table were incorrect)

Any positive points are earned points, while negative points are redeemed. Due to FIFO (the first in the 1st concept) from the second batch of points spent (-400), 100 of them were taken from points earned in 20150101 (in the British format) and 300 from 20151101.

The goal is to calculate for each client the number of points spent during x and y months of earnings. Thanks again for your help.

Answer 1

I already answered a similar question here and here

You need to break the points received and redeemed by single units, and then link them so that each point earned corresponds to the redeemed point.

For each of these matching lines, the months elapsed from earnings to redemption are calculated, and then all are summed.

For FN_NUMBERS (n) this is a counting table, see the other answers that I linked above.

 ;with p as (select * from CustomerPoints), e as (select * from p where points>0), r as (select * from p where points<0), ex as ( select *, ROW_NUMBER() over (partition by CustomerID order by [date] ) rn from e join FN_NUMBERS(1000) on N<= e.points ), rx as ( select *, ROW_NUMBER() over (partition by CustomerID order by [date] ) rn from r join FN_NUMBERS(1000) on N<= -r.points ), j as ( select ex.CustomerID, DATEDIFF(month,ex.date, rx.date) mm from ex join rx on ex.CustomerID = rx.CustomerID and ex.rn = rx.rn and rx.date>ex.date ) -- use this select to see points redeemed in current and past semester select * from j join (select 0 s union all select 1 s ) p on j.mm >= (ps*6)+(ps) and j.mm < ps*6+6 pivot (count(mm) for s in ([0],[2])) p order by 1, 2 -- use this select to see points redeemed with months detail --select * from j pivot (count(mm) for mm in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) p order by 1 -- use this select to see points redeemed in rows per month --select CustomerID, mm, COUNT(mm) PointsRedeemed from j group by CustomerID, mm order by 1 

default query output, 0 - 0-6 months, 1 - 7-12 (foreclosure age in months)

 CustomerID 0 1 1 700 100 

the conclusion of the second request, 0..12 is the repayment age in months

 CustomerID 0 1 2 3 4 5 6 7 8 9 10 11 12 1 0 700 0 0 0 0 0 0 0 0 0 100 0 

the conclusion from the third request is the repayment age in months

 CustomerID mm PointsRedeemed 1 1 700 1 11 100 

goodbye