Generate Date Range Column from Two Date Columns

lvtrujillot

Hi all !

Im trying to generate a date column with a range of dates from two columns:

Table I have (date format (mm/dd/yyyy) )

Id   start_date   end_date    field1
1    01/01/2019   07/01/2019  text1
1    07/01/2019   12/01/2019  text2
2    01/01/2019   02/01/2019  text3

The idea is to generate a list of dates between start and end date with an interval of 1 month.

Output Table :

Id date        field1
1  01/01/2019  text1
1  02/01/2019  text1
1  03/01/2019  text1
1  04/01/2019  text1
1  05/01/2019  text1
1  06/01/2019  text1
1  07/01/2019  text2
1  10/01/2019  text2
1  11/01/2019  text2
1  12/01/2019  text2
2  01/01/2019  text3
2  02/01/2019  text2
 

Any idea how could I get this ?

Appreciate the help! Thanks

GrantSmith

I just posted something for a similar question but you can use the calendar.csv dataset from the Domo Dimensions connector.

Then you can use MySQL to get your desired output (MagicETL doesn't support conditional joins like this directly. You'd have to do a cartesian join [add constant column to both - say a value of 1 - and then join both datasets on that value then use a filter tile to filter your dataset down. This would exponentially increase the amount of records MagicETL would have to process and isn't as straight forward as MySQL in this case)

select `Id`, `dt` as date, `field1`
FROM `calendar_dates` as cd
JOIN `my_dataset` ds on cd.dt BETWEEN ds.`start_date` and ds.`end_date` and cd.d = 1

cd.d=1 just tells it to return the record for the first day of the month since you're wanting to do it on a monthly basis and your data appears to use the first day to represent each month.

GrantSmith

I just posted something for a similar question but you can use the calendar.csv dataset from the Domo Dimensions connector.

Then you can use MySQL to get your desired output (MagicETL doesn't support conditional joins like this directly. You'd have to do a cartesian join [add constant column to both - say a value of 1 - and then join both datasets on that value then use a filter tile to filter your dataset down. This would exponentially increase the amount of records MagicETL would have to process and isn't as straight forward as MySQL in this case)

select `Id`, `dt` as date, `field1`
FROM `calendar_dates` as cd
JOIN `my_dataset` ds on cd.dt BETWEEN ds.`start_date` and ds.`end_date` and cd.d = 1

cd.d=1 just tells it to return the record for the first day of the month since you're wanting to do it on a monthly basis and your data appears to use the first day to represent each month.

lvtrujillot

@GrantSmith That's exactly what I need. Thank you!