How Separate Dates?

[Anonymous]

Hi All,

below is sample data:

Id  Startdate                             Enddate

1   2-jan-2017 00:00:00:00        4 -jan -2017 23:59:59:59

expected Output:

Id    Date         Hours

1   2-1-2017      24

1   3-1-2017      24

1   4-1-2017      24

Thanks & Regards,

Nagesh

[Chanty4u]

try like below  with your fields

[Anil_Babu_Samineni · Accepted solution]

Does you need Script level?

If so, May be do this using Master calendar to generate missing dates

Or

Directory1:

load * Inline [

Id ,Startdate, Enddate

1 ,2-jan-2017 00:00:00:00 ,4-jan-2017 23:59:59:59

];

Directory2:

load

Id,

Min(Date(Date#(Startdate,'DD-MMM-YYYY hh:mm:ss:ff'),'DD-MM-YYYY')) AS MinDate,

Max(Date(Date#(Enddate,'DD-MMM-YYYY hh:mm:ss:ff'),'DD-MM-YYYY')) AS MaxDate

Resident Directory1

GROUP BY Id;

Directory3:

LOAD

RowNo() as SNo,

Id,

IF(Id<>Previous(Id),(MinDate),DATE((Peek(Date,-1)+1),'DD-MM-YYYY')) AS Date

Resident Directory2

while MinDate+IterNo()-1<=MaxDate ;

drop Table Directory1,Directory2;

And Front end it seems this?