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
ID | Allocated | RATE TYPE | DATE |
1 | A | 6 | 10/01/2014 |
2 | A | 2 | 20/02/2014 |
3 | A | 2 | 19/10/2014 |
4 | A | 2 | 11/12/2014 |
5 | B | 6 | 7/01/2015 |
6 | B | 2 | 10/02/2015 |
7 | B | 2 | 20/03/2015 |
8 | C | 6 | 29/03/2015 |
9 | C | 2 | 12/04/2015 |
10 | C | 2 | 29/04/2015 |
MY OUTPUT
ID | Allocated | RATE TYPE | STARTDATE | ENDDATE |
1 | A | 6 | 10/01/2014 | 20/02/2014 | SO WHENEVER RATETYPE '6' I AM NOT ADDING 1 DAY TO IT |
2 | A | 2 | 21/02/2014 | 19/10/2014 |
3 | A | 2 | 20/10/2014 | 11/12/2014 |
4 | A | 2 | 12/12/2014 | 6/01/2015 | FOR ROW JUST BEFORE '6' SUBTRACTING END DATE BY 1 |
5 | B | 6 | 7/01/2015 | 10/02/2015 | SO WHENEVER RATETYPE '6' I AM NOT ADDING 1 DAY TO IT |
6 | B | 2 | 11/02/2015 | 20/03/2015 |
7 | B | 2 | 21/03/2015 | 28/03/2015 | FOR ROW JUST BEFORE '6' SUBTRACTING END DATE BY 1 |
8 | C | 6 | 29/03/2015 | 12/04/2015 | SO WHENEVER RATETYPE '6' I AM NOT ADDING 1 DAY TO IT |
9 | C | 2 | 13/04/2015 | 29/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