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 |
