Date Load

[Vinothishere]

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.

[CELAMBARASAN]

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
];

[anbu1984 · Accepted solution]

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 ];

[Vinothishere]

can you please explain how it works?

[anbu1984]

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

[Vinothishere]

thank you very much