crystaldev1
Programmer
Hello, I have following dataset and need assistance with query to meet the following requirements:
PAYMENTKEY CUSTOMERKEY DATE PAYMETHOD RESPONSE
1 1 11/1 C A
2 1 1/3 C A
3 1 1/5 C D
4 1 3/6 C A
5 1 3/7 C A
21 2 1/3 C A
25 2 3/2 C A
27 2 3/10 C A
29 3 2/01 C D
31 3 2/02 D A
40 3 2/05 C A
-For each paymentkey, I would like to get a count of consecutive records prior to curent PAYMENTKEY where PAYMETHOD = C and RESPONSE = A.
-Prior records has to be within 60 days from the same customerkey.
-Only interested in consecutive records since the last D (denied) response. So if there was payment with response of D 1 day before, then consecutive record would be 0. Don't care about anything prior to D (if there is a D response).
Results would be following:
Paymentkey ConsecutiveCount
1 0
2 1
3 2
4 0
5 1
21 0
25 1
27 1 (has to be within 60 days)
29 0
31 0
40 1
Thanks!
PAYMENTKEY CUSTOMERKEY DATE PAYMETHOD RESPONSE
1 1 11/1 C A
2 1 1/3 C A
3 1 1/5 C D
4 1 3/6 C A
5 1 3/7 C A
21 2 1/3 C A
25 2 3/2 C A
27 2 3/10 C A
29 3 2/01 C D
31 3 2/02 D A
40 3 2/05 C A
-For each paymentkey, I would like to get a count of consecutive records prior to curent PAYMENTKEY where PAYMETHOD = C and RESPONSE = A.
-Prior records has to be within 60 days from the same customerkey.
-Only interested in consecutive records since the last D (denied) response. So if there was payment with response of D 1 day before, then consecutive record would be 0. Don't care about anything prior to D (if there is a D response).
Results would be following:
Paymentkey ConsecutiveCount
1 0
2 1
3 2
4 0
5 1
21 0
25 1
27 1 (has to be within 60 days)
29 0
31 0
40 1
Thanks!