Split date ranges by day

[krmvacar]

Hi All,

I have a data set as follows. I want to divide the data daily, but I could not succeed. Please help.

ORDERID	STARTDATE	ENDDATE
UGR190643	2024-01-01 23:45	2024-01-02 15:30
UGR190644	2024-01-04 16:27	2024-01-08 19:30
UGR190645	2024-01-05 14:30	2024-01-05 18:30

The code I wrote and the result I want are as follows:

Data:
LOAD
ORDERID,
Timestamp(STARTDATE) as STARTDATE,
Timestamp(ENDDATE) as ENDDATE
INLINE [
ORDERID, STARTDATE, ENDDATE
UGR190643, 2024-01-01 23:45, 2024-01-02 15:30
UGR190644, 2024-01-05 16:27, 2024-01-08 19:30
UGR190645, 2024-01-05 14:30, 2024-01-05 18:30
];

TempTable:
LOAD
ORDERID,
STARTDATE + IterNo() - 1 AS TempStart,
STARTDATE,
ENDDATE
Resident Data
While STARTDATE + IterNo() - 1 <= ENDDATE;

FinalTable:
LOAD
ORDERID,
TempStart AS STARTDATE,
Timestamp(
If(Floor(TempStart) = Floor(ENDDATE),
ENDDATE,
Floor(TempStart) + 1 - (1/86400))
) AS ENDDATE
Resident TempTable
Order By ORDERID, STARTDATE;

DROP Table TempTable;

Exit Script;

I want to result:

ORDERID	STARTDATE	ENDDATE
UGR190643	2024-01-01 23:45	2024-01-01 23:59
UGR190643	2024-01-02 00:00	2024-01-02 15:30
UGR190644	2024-01-04 16:27	2024-01-04 23:59
UGR190644	2024-01-05 00:00	2024-01-05 23:59
UGR190644	2024-01-06 00:00	2024-01-06 23:59
UGR190644	2024-01-07 00:00	2024-01-07 23:59
UGR190644	2024-01-08 00:00	2024-01-08 19:30
UGR190645	2024-01-05 14:30	2024-01-05 18:30

Thanks

[marksouzacosta · Accepted solution]

Nice one @krmvacar.

Following my solution:

Data:
LOAD
    ORDERID,
    Date(Floor(Timestamp(STARTDATE)-1)) AS MinDate,
    Date(Floor(Timestamp(ENDDATE)+1)) AS MaxDate,
    Timestamp(STARTDATE) as STARTDATE,
    Timestamp(ENDDATE) as ENDDATE
INLINE [
    ORDERID, STARTDATE, ENDDATE
    UGR190643, 2024-01-01 23:45, 2024-01-02 15:30
    UGR190644, 2024-01-04 16:27, 2024-01-08 19:30
    UGR190645, 2024-01-05 14:30, 2024-01-05 18:30
];

TempTable:
LOAD
    ORDERID,
    MinDate - 1 + IterNo() AS TempDate,
    STARTDATE,
    ENDDATE
Resident
	Data
While
	MinDate - 1 + IterNo() <= MaxDate
;

DROP TABLE Data;

FinalTable:
LOAD
    ORDERID,
    If(TempDate >= STARTDATE,TempDate,STARTDATE) AS [STARTDATE],
    Timestamp(If(Floor(TempDate) = Floor(ENDDATE), ENDDATE, Floor(TempDate) + 1 - (1/86400))) AS [ENDDATE]
Resident
	TempTable
WHERE
	TempDate >= Floor(STARTDATE)
    AND TempDate <= Floor(ENDDATE)
Order By
	ORDERID,
    TempDate
;

DROP TABLE TempTable;

One quick note: in your Inline Load example you said
UGR190644, 2024-01-05 16:27, 2024-01-08 19:30

But the correct is:
UGR190644, 2024-01-04 16:27, 2024-01-08 19:30

Regards,

Mark Costa