How to get rows generated for week start dates between two date columns

[Santhoshraj]

Hi,

I have a table like this :

Case	Parts	NAME	creatdt	compdt
1	A	ea	12/15/2023	12/19/2023
2	B	sa	08/02/2023	09/26/2023
3	C	da	12/26/2023	12/28/2023
4	D	fd	02/06/2024	02/06/2024
5	E	gl	09/12/2023	09/12/2023
6	F	fd	11/30/2023	12/07/2023
7	G	df	01/03/2024	01/05/2024
8	H	yr	02/13/2024	02/13/2024

I want to plot a graph for open cases during the start of the week (Monday). So i thought of coming up something like the below table but not sure how to achieve this. Can someone please help me with this ?

Case	Parts	NAME	creatdt	compdt	weekstartopen
1	A	ea	12/15/2023	12/19/2023	12/18/2023
2	B	sa	08/02/2023	09/26/2023	07/31/2023
2	B	sa	08/02/2023	09/26/2023	08/07/2023
2	B	sa	08/02/2023	09/26/2023	08/14/2023
2	B	sa	08/02/2023	09/26/2023	08/21/2023
2	B	sa	08/02/2023	09/26/2023	08/28/2023
2	B	sa	08/02/2023	09/26/2023	09/04/2023
2	B	sa	08/02/2023	09/26/2023	09/11/2023
2	B	sa	08/02/2023	09/26/2023	09/18/2023
2	B	sa	08/02/2023	09/26/2023	09/25/2023
3	C	da	12/26/2023	12/28/2023	12/25/2023
4	D	fd	02/06/2024	02/06/2024	02/05/2024
5	E	gl	09/12/2023	09/12/2023	09/11/2023
6	F	fd	11/30/2023	12/07/2023	11/27/2023
6	F	fd	11/30/2023	12/07/2023	12/04/2023
7	G	df	01/03/2024	01/05/2024	01/01/2024
8	H	yr	02/13/2024	02/26/2024	02/12/2024
8	H	yr	02/13/2024	02/26/2024	02/19/2024
8	H	yr	02/13/2024	02/26/2024	02/26/2024

[ChrisTX · Accepted solution]

Try the attached workflow

 

Chris

[OllieClarke · Accepted solution]

Hey @Santhoshraj,

Your output is a little inconsistent, but I think this is what you're after:

The generate rows is the key:

I set the initialisation expression as the monday of the week of the creatdt, and then we can simply create a new date in 7 day increments until we hit the compdt.

Hope that helps,

Ollie

[Santhoshraj]

Great. It worked. Thanks for the help both. @OllieClarke @ChrisTX