Spiliting start date and end date in day wise

[Anonymous]

Hi Folks

Can  any one help me below scenario  ,   i was facing problems in it .

I am having  sample data as follows

Id  Startdate                             Enddate

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

I want to break the start date and end date in day wise  as follows

Required Output

row no  Id  Date         Hours

1          1   2-1-2017  24

2          1   3-1-2017  24

3          1   4-1-2017  24

Can  any one help me on this issue .

[#2]

Try Like this

Its working 

a: !
load * Inline [
Id ,Startdate, Enddate
1 ,2-jan-2017 00:00:00:00 ,4-jan-2017 23:59:59:59
2 ,3-jan-2017 00:00:00:00 ,5-jan-2017 23:59:59:59
];

b:

load
Id,
MIN(DATE(date#(left(Startdate,10),'DD-MMM-YYYY'),'DD/MM/YYYY')) AS MIN,
MAX(DATE(date#(left(Enddate,10),'DD-MMM-YYYY'),'DD/MM/YYYY')) AS MAX
Resident a
GROUP BY Id;

C:
LOAD
Id,
IF(Id<>Previous(Id),(MIN),DATE((Peek(GENERATE,-1)+1),'DD/MM/YYYY')) AS GENERATE
Resident b
while MIN+IterNo()-1<=MAX ;
drop Table a,b;

[#3]

ADD ROWNO() IN  3rd table for   Row column

[rahulpawarb]

Hello Mahesh,

Please refer below given sample script:

Data:
LOAD * INLINE [
Id, Startdate, Enddate
1, 2-jan-2017 00:00:00, 4-jan-2017 23:59:59
];


LET vId = Peek ('Id', 0, 'Data');
LET vStartDate = Num(TimeStamp#(Peek('Startdate', 0, 'Data'), 'DD-MMM-YYYY hh:mm:ss'));
LET vEndDate = Num(TimeStamp#(Peek('Enddate', 0, 'Data'), 'DD-MMM-YYYY hh:mm:ss'));


Output:
LOAD 
RowNo() AS RowNo,
$(vId) AS Id,
Date($(vStartDate) + IterNo()-1, 'DD/MM/YYYY') AS Date,
24 AS Hours
Autogenerate 1
WHILE $(vStartDate) + IterNo() - 1 <= $(vEndDate);

Regards!

Rahul