I been trying to handle the below logic using unbounded preceeding for the past few days..Can you please have a look?
mbr amt1 amt2
123 50 70
123 50 90
123 50 100
I am expecting output in the format
mbr amt1 amt2 amt3
123 50 70 20 [ ie 70-50]
123 50 90 20 [ ie (90-50) - 20]
123 50 100 10 [ ie (100-50) - 20(2nd amt3) -20(1st amt3)]
Can you please tell me whether this can be achieved in TD?
Is the data sorted by amt2?
Rephrasing your desired result you need the current difference minus the previous difference, like this:
(amt2 - amt1)
- coalesce(sum (amt2 - amt1)
over (partition by mbr
order by amt2
rows between 1 preceding and 1 preceding
Btw, why do you need this result, what's the business question?
Thank you Dieter
The data will be sorter based on the chkdt field. Sorry to miss that.
mbr chkdt amt1 amt2
123 11/01/11 50 70
123 11/05/11 50 90
123 11/10/11 50 100
mbr chkdt amt1 amt2 amt3
123 11/01/11 50 70 20 [ ie 70-50]
123 11/05/11 50 90 20 [ ie (90-50) - 20]
123 11/10/11 50 100 10 [ ie (100-50) - 20(2nd amt3) -20(1st amt3)]
amt3 is the incetive paid to a member each time.
First time paid 20
Second time 90-50=40. But since we already paid 20, 40-20 ie 20
Third time 100-50=50. We already paid 20 + 20 = 40. so 50-40=10
This can repeat until the correct amount is paid to the member.
Also Dieter couldn't we use MDIFF function also to handle this logic.
MBR,chkdt,AMT1,AMT2 , AMT2-AMT1 AS AMT3,
CASE when mdiff(amt3,1,chkdt) is null then amt3 else mdiff(amt3,1,chkdt) end as inct_paid
Could anyone provide the suggestion for the below query,
The requirement is, considering 2 columns with date and amount, if the month of the date is 1(January) then the amount should be the same, in case if it is 2,3,..,12 then the amount should be the difference with last month.
Date - Amount - Diff
01/01/2016 - 100 - 100
01/02/2016 - 20 - 80
02/02/2016 - 500 -500
02/03/2016 -250 -250
Thanks in Advance,
Why is the result of the 3rd row 500 (and not 500-80 = 420)? Should there be no subtraction within a month? Also is the date format in example given DD/MM/YYYY?
I'm a bit stuck ... I have 3 tables (table a, b and c). table a contains the base population with two columns 'ID' and 'Start_Date'. table b contains a 'Required_Amt' field that can be joined by the same 'ID'. Table c has the complete payment transactions for each 'ID' from table a.
I would like to take the cumulative sum from table c only for the amounts received after the 'start_date' from table a. I would also like to reference the date and cumulative amount once the cumulative amount is >= the 'Requited_Amt' from table b. Basically I would like the sum to stop calculating anything additional payments received after the required amount and received date have been met. In other words, if 5 payments were received since the start_date, and the req_amt was met on the 4th payment. I'd like to see the date and amount for that 4th payment.
I'm getting close but not quite there yet. I've tried playing around with the sum partition by and unbounded preceding options but to no avail. Here is a sample starter code.
SELECT a.ID, a.START_DATE, b.REQ_AMT, c.REC_DT, c.TOTAL_RECD FROM a LEFT JOIN (SELECT ID, REQ_AMT FROM b) b ON a.ID = b.ID LEFT JOIN (SELECT ID, TOTAL_RECD, REC_DT FROM c WHERE TRAN_ID = '173' AND TOTAL_RECD>0) c ON a.ID = c.ID AND c.REC_DT >= a.START_DT