SQL depended on previous rows

Analytics
N/A

SQL depended on previous rows

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

Tags (3)
1 REPLY

Re: SQL depended on previous rows

Hi gkcy,

 

This looks very much like a question that has been posted before.

 

Have a look at a possible answer here:

https://community.teradata.com/t5/Database/Need-help-calculating-current-row-depended-on-previous-ro... 

 

Chers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com