Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
try like below with your fields
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?