Hi all,
I am having a trouble writing an sql query. I have the below data:
Account | Date | Flag | Grace Period |
1 | 2017-07-31 | 0 | 2017-03-31 |
1 | 2017-08-31 | 0 | 2017-03-31 |
1 | 2017-09-30 | 1 | 2017-11-30 |
1 | 2017-10-31 | 0 | 2017-11-30 |
1 | 2017-11-30 | 1 | 2018-02-28 |
1 | 2017-12-31 | 0 | 2018-02-28 |
1 | 2018-01-31 | 0 | 2018-02-28 |
1 | 2018-02-28 | 1 | 2018-04-30 |
1 | 2018-03-31 | 1 | 2018-05-31 |
1 | 2018-04-30 | 0 | 2018-05-31 |
1 | 2018-05-31 | 0 | 2018-05-31 |
1 | 2018-06-30 | 0 | 2018-05-31 |
1 | 2018-07-31 | 0 | 2018-05-31 |
1 | 2018-08-31 | 1 | 2018-10-31 |
And what i want is: Whenever the flag is set to 1 then the account has an additional grace period which is calculated as Grace Period End + 2 months.
During this new calculated period if a flag comes as 1, then do not calculated a new grace period and keep the current value.
Once the new calculated period is ended (Date > New Calculated Period) then if flag = 1 calculated the new grace period end. At the end, I want a cumulative sum of the valid Flag = 1.
To be more specific this is what the above data should look like
Account | Date | Flag | Grace Period | New Grace Period | Valid Flag Counter |
1 | 2017-07-31 | 0 | 2017-03-31 | - |
|
1 | 2017-08-31 | 0 | 2017-03-31 | - |
|
1 | 2017-09-30 | 1 | 2017-11-30 | 2018-01-31 | 1 |
1 | 2017-10-31 | 0 | 2017-11-30 | 2018-01-31 | 1 |
1 | 2017-11-30 | 1 | 2018-02-28 | 2018-01-31 | 1 |
1 | 2017-12-31 | 0 | 2018-02-28 | 2018-01-31 | 1 |
1 | 2018-01-31 | 0 | 2018-02-28 | 2018-01-31 | 1 |
1 | 2018-02-28 | 1 | 2018-04-30 | 2018-06-30 | 2 |
1 | 2018-03-31 | 1 | 2018-05-31 | 2018-06-30 | 2 |
1 | 2018-04-30 | 0 | 2018-05-31 | 2018-06-30 | 2 |
1 | 2018-05-31 | 0 | 2018-05-31 | 2018-06-30 | 2 |
1 | 2018-06-30 | 0 | 2018-05-31 | 2018-06-30 | 2 |
1 | 2018-07-31 | 0 | 2018-05-31 | 2018-06-30 | 2 |
1 | 2018-08-31 | 1 | 2018-10-31 | 2018-12-31 | 3 |
Hi gkcy,
This looks very much like a question that has been posted before.
Have a look at a possible answer here:
Chers,
Dave
Hi Dave,
this is interesting and your solution perfectly works.. but just trying if it can be written in signle SQL stmt using window analytical functions and unsuccessful so far in covering all scenarios... any experts, please welcome.
I don't think that you can get this using OVER.
You probably need some kind of recursion, either WITH RECURSIVE or loops in a Stored Procedure.
Thanks for confirmation Dieter,