Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle Lead Lag function :: Date

3e6dc18f-fd18-46cc-a177-985399fc6b57May 10 2015 — edited May 10 2015

Hi ,

I was thinking to use lead lag function for below input , but fail to understand the required function to stop the function in between when there is a change in Rate Type,  can anyone please suggest any possible options ---

MY INPUT

   

IDAllocatedRATE TYPEDATE
1A610/01/2014
2A220/02/2014
3A219/10/2014
4A211/12/2014
5B67/01/2015
6B210/02/2015
7B220/03/2015
8C629/03/2015
9C212/04/2015
10C229/04/2015

MY OUTPUT

      

IDAllocatedRATE TYPESTARTDATEENDDATE
1A610/01/201420/02/2014     SO WHENEVER RATETYPE '6' I AM NOT ADDING 1 DAY TO IT
2A221/02/201419/10/2014
3A220/10/201411/12/2014
4A212/12/20146/01/2015      FOR ROW JUST BEFORE '6' SUBTRACTING END DATE BY 1
5B67/01/201510/02/2015      SO WHENEVER RATETYPE '6' I AM NOT ADDING 1 DAY TO IT
6B211/02/201520/03/2015
7B221/03/201528/03/2015       FOR ROW JUST BEFORE '6' SUBTRACTING END DATE BY 1
8C629/03/201512/04/2015       SO WHENEVER RATETYPE '6' I AM NOT ADDING 1 DAY TO IT
9C213/04/201529/04/2015

I am using Lead function to define the end date , but unable to add that extra flavour that gets calculated on Rate Type, whenever its 6 the row date itself changes and the row before that changes.

Need your help on this please.

Thank you

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 7 2015
Added on May 10 2015
1 comment
2,137 views