Hi All..
I have Data Something like Below.
LOAD * INLINE [
ID, Begin, End, Action Type
1, 1-Jan-2015, 30-Mar-2016, ZA
1, 31-Mar-2016, 31-Aug-2016, ZL
1, 1-Oct-2016, 31-Dec-2016, ZJ
2, 1-Jan-2015, 30-Mar-2016, ZA
2, 31-Mar-2016, 30-Aug-2016, ZL
2, 31-Aug-2016, 31-Dec-2016, ZJ
];
My Condition is, if Action Type = ZJ and Begin Date Month and year equal to Previous End Date Month and Year so deduct 1 month from Previous End Date.
For ID 1 When Action Type is ZJ , its Begin Date is 1-Oct-2016 and its Previous End Date is 31-Aug-2016 , Both are in Different Month So Nothing to Happend.
Required OutPut:
ID, Begin, End, Action Type , NEW End Date
1, 1-Jan-2015, 30-Mar-2016, ZA , 30-Mar-2016
1, 31-Mar-2016, 31-Aug-2016, ZL, 31-Aug-2016
1, 1-Oct-2016, 31-Dec-2016, ZJ,31-Dec-2016
But in ID 2
When Action Type is ZJ and its Begin Date is 31-Aug-2016 and its Previous End Date is 30-Aug-2016. Both are in Same month and year(AUG-2016) . So it should deduct one month from Previous End Date.
Required OutPut:
ID, Begin, End, Action Type , NEW End Date
2, 1-Jan-2015, 30-Mar-2016, ZA ,30-Mar-2016
2, 31-Mar-2016, 30-Aug-2016, ZL,30-Jul-2016
2, 31-Aug-2016, 31-Dec-2016, ZJ,31-Dec-2016
Kindly help me resolved this
