Populate date between 2 dates

[harson]

Hello,

Any advice appreciated.

I have data like below:

ID	CreateDate	ClosedDate	Status
VR00001	01/09/2020		Open
VR00002	01/09/2020	05/09/2020	Closed
VR00003	05/09/2020	06/09/2020	Closed
VR00004	05/09/2020	05/09/2020	Closed

And I need to populate data between CreateDate and ClosedDate. If ClosedDate is null, I take the current date as the closing date but keeping the status in "Open".

As an example of the expected result below, I took Date(Today())=05/09/2020

I need it to be like below :

ID	Date	Status
VR00001	01/09/2020	Open
VR00001	02/09/2020	Open
VR00001	03/09/2020	Open
VR00001	04/09/2020	Open
VR00001	05/09/2020	Open
VR00002	01/09/2020	Open
VR00002	02/09/2020	Open
VR00002	03/09/2020	Open
VR00002	04/09/2020	Open
VR00002	05/09/2020	Closed
VR00003	05/09/2020	Open
VR00003	06/09/2020	Closed
VR00004	05/09/2020	Closed

Cheers,

[Taoufiq_Zarra · Accepted solution]

@harson  If ClosedDate is null, I take the current date as the closing =>08/10/2020, if so , one solution:

Input:

LOAD ID, CreateDate,Date(if(len(trim(ClosedDate))=0,today(),ClosedDate)) as ClosedDate , Status INLINE [
    ID, CreateDate, ClosedDate, Status
    VR00001, 01/09/2020,, Open
    VR00002, 01/09/2020, 05/09/2020, Closed
    VR00003, 05/09/2020, 06/09/2020, Closed
    VR00004, 05/09/2020, 05/09/2020, Closed
];


Tmp:
noconcatenate


load ID,Date,if(Status='Closed' and Date<ClosedDate,'Open',Status) as Status;
load *,Date(CreateDate+IterNo()-1) as Date resident Input
while CreateDate+IterNo()-1<=ClosedDate;

drop table Input;

output: