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