Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a straight table which gives me the below output based on my expressions.
Key | CARD_NUMBER | Doctor | Days_7 | Date | Paid Amt | Recovery Amt |
1294 | 17 | 0250888 | 0 | 30/Jan/2021 | 400 | 0 |
1294 | 17 | 0250888 | 0 | 18/Mar/2021 | 400 | 0 |
1294 | 18 | 0250888 | 0 | 14/Jan/2021 | 400 | 0 |
1294 | 19 | 0250888 | 0 | 17/Feb/2021 | 450 | 0 |
1294 | 19 | 0250888 | 0 | 13/Mar/2021 | 450 | 0 |
1294 | 21 | 0250888 | 0 | 11/Mar/2021 | 450 | 0 |
1294 | 21 | 0250888 | 7 | 18/Mar/2021 | 450 | 450 |
Recovery Amount is checking the combination of Key, Card no, Doctor if all of these are the same for two different transactions within 7 days then its recovered or else it will be 0. which I have achieved.
But, now I want to show only those cards which have some recovery against them, So I should only get last line as my output along with the first transaction of that card within 7 days. (there can be cases where there are multiple transactions between day 0 to 7)
Key | CARD_NUMBER | Doctor | Days_7 | Date | Paid Amt | Recovery Amt |
1294 | 21 | 0250888 | 0 | 11/Mar/2021 | 450 | 0 |
1394 | 21 | 0250888 | 7 | 18/Mar/2021 | 450 | 450 |
Any thoughts on how can I achieve this?
Can anyone help me with this, I am stuck with this requirement