SQL depended on previous rows

Analytics
Fan

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)
4 REPLIES
Senior Apprentice

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
Enthusiast

Re: SQL depended on previous rows

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.

Highlighted
Junior Contributor

Re: SQL depended on previous rows

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.

Enthusiast

Re: SQL depended on previous rows

Thanks for confirmation Dieter,