Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following data
Id StartDate EndDate
100 5/1/2014 19:20:00 5/1/2014 23:59:59
101 6/1/2014 10:20:00 8/1/2014 19:30:00
Since Id:101 having the different start date and end date,I need to load it in the following manner:
Id StartDate EndDate
100 5/1/2014 19:20:00 5/1/2014 23:59:59
101 6/1/2014 10:20:00 6/1/2014 23:59:59
101 7/1/2014 00:00:00 7/1/2014 23:59:59
101 8/1/2014 00:00:00 8/1/2014 19:30:00
Thanks in Advance.
Load Id,If(IterNo() = 1,StartDate,Timestamp(StrtDt+IterNo()-1,'D/M/YYYY h:mm:ss')) As StartDate,
If(StrtDt + IterNo()-1 = EndDt,EndDate,Timestamp(StrtDt+IterNo()-1+Time#('23:59:59','hh:mm:ss'),'D/M/YYYY h:mm:ss')) As EndDate
While StrtDt + IterNo()-1 <= EndDt;
Load *,Date(Floor(StartDate)) As StrtDt,Date(Floor(EndDate)) As EndDt;
Load Id,Timestamp#(StartDate,'DD/MM/YYYY hh:mm:ss') As StartDate,Timestamp#(EndDate,'DD/MM/YYYY hh:mm:ss') As EndDate Inline [
Id,StartDate,EndDate
100,5/1/2014 19:20:00,5/1/2014 23:59:59
101,6/1/2014 10:20:00,8/1/2014 19:30:00 ];
Try this
LOAD
Id,
Timestamp(StartDate + 1 -1) < EndDate as it,
If(IterNo()>1, DayStart(Timestamp(StartDate+Iterno()-1)), StartDate) as StartDate,
If(DayStart(StartDate + IterNo() -1) = DayStart(EndDate), EndDate, DayEnd(Timestamp(EndDate+Iterno()-1))) as EndDate
While DayStart(StartDate + IterNo() -1) < EndDate;
LOAD Id, TimeStamp(Timestamp#(StartDate,'D/M/YYYY H:mm:ss')) AS StartDate,TimeStamp(Timestamp#(EndDate,'D/M/YYYY H:mm:ss')) AS EndDate INLINE [
Id ,StartDate ,EndDate
100 ,5/1/2014 19:20:00 ,5/1/2014 23:59:59
101 ,6/1/2014 10:20:00 ,8/1/2014 19:30:00
];
Load Id,If(IterNo() = 1,StartDate,Timestamp(StrtDt+IterNo()-1,'D/M/YYYY h:mm:ss')) As StartDate,
If(StrtDt + IterNo()-1 = EndDt,EndDate,Timestamp(StrtDt+IterNo()-1+Time#('23:59:59','hh:mm:ss'),'D/M/YYYY h:mm:ss')) As EndDate
While StrtDt + IterNo()-1 <= EndDt;
Load *,Date(Floor(StartDate)) As StrtDt,Date(Floor(EndDate)) As EndDt;
Load Id,Timestamp#(StartDate,'DD/MM/YYYY hh:mm:ss') As StartDate,Timestamp#(EndDate,'DD/MM/YYYY hh:mm:ss') As EndDate Inline [
Id,StartDate,EndDate
100,5/1/2014 19:20:00,5/1/2014 23:59:59
101,6/1/2014 10:20:00,8/1/2014 19:30:00 ];
can you please explain how it works?
While StrtDt + IterNo()-1 <= EndDt; -- Iterate for number of days between start and end date
If(IterNo() = 1,StartDate,Timestamp(StrtDt+IterNo()-1,'D/M/YYYY h:mm:ss')) AsStartDate -- In first iteration assign StartDate from your input else keep adding one day to StartDate
If(StrtDt + IterNo()-1 = EndDt,EndDate,Timestamp(StrtDt+IterNo()-1+Time#('23:59:59','hh:mm:ss'),'D/M/YYYY h:mm:ss')) As EndDate -- If StartDate + IterNo is equal to EndDate, then assign EndDate from your input else keep adding one day to your StartDate and keep time as 23:59:59
thank you very much