Joining and getting all dates between start and end date

TMonty0319

Im doing a join on billing ID's. Each billing ID has a start date and an end date. Im trying to return all dates between start and end date for each billing ID. I tried adding this formula to join.

dt BETWEEN Service Start Date and Service End Date

It isn't working as expected. Im just getting one date for each billing ID

hanmari

Here is some SQL that will build a sequential list of days for a preset set of years. You can then LEFT JOIN against your data to set the values for each date:

WITH years AS (

SELECT 2020 AS year UNION SELECT 2021 AS year UNION SELECT 2023 AS year UNION SELECT 2024 AS year

),

months AS (

SELECT 1 AS month UNION SELECT 2 AS month UNION SELECT 3 AS month UNION SELECT 4 AS month UNION

SELECT 5 AS month UNION SELECT 6 AS month UNION SELECT 7 AS month UNION SELECT 8 AS month UNION

SELECT 9 AS month UNION SELECT 10 AS month UNION SELECT 11 AS month UNION SELECT 12 AS month

),

days AS (

SELECT 1 AS day UNION SELECT 2 AS day UNION SELECT 3 AS day UNION SELECT 4 AS day UNION SELECT 5 AS day UNION

SELECT 6 AS day UNION SELECT 7 AS day UNION SELECT 8 AS day UNION SELECT 9 AS day UNION SELECT 10 AS day UNION

SELECT 11 AS day UNION SELECT 12 AS day UNION SELECT 13 AS day UNION SELECT 14 AS day UNION SELECT 15 AS day UNION

SELECT 16 AS day UNION SELECT 17 AS day UNION SELECT 18 AS day UNION SELECT 19 AS day UNION SELECT 20 AS day UNION

SELECT 21 AS day UNION SELECT 22 AS day UNION SELECT 23 AS day UNION SELECT 24 AS day UNION SELECT 25 AS day UNION

SELECT 26 AS day UNION SELECT 27 AS day UNION SELECT 28 AS day UNION SELECT 29 AS day UNION SELECT 30 AS day UNION

SELECT 31 AS day

),

all_dates AS (

SELECT DATE(STR_TO_DATE(CONCAT(years.year, '-', months.month, '-', days.day), '%Y-%m-%d')) AS day

FROM years

CROSS JOIN months

CROSS JOIN days

WHERE DATE(STR_TO_DATE(CONCAT(years.year, '-', months.month, '-', days.day), '%Y-%m-%d')) IS NOT NULL

)

SELECT day

FROM all_dates

WHERE day BETWEEN '2021-04-30' AND '2024-10-23'

ORDER BY day

GrantSmith

Using magic ETL, take the date dimension dataset (Domo Dimensions connector) and feed it into a Add Constant tile to create a new field called "join column" with a value of 1. Do the same for your input dataset. Feed both of those into the join based on the join column. Then you feed that into a filter field with your BETWEEN clause. The joining on the join column will do a cartesian join and add all dates for every record then you're filtering out the rows where the dates from the dimension dataset aren't within your range.

hanmari

Here is some SQL that will build a sequential list of days for a preset set of years. You can then LEFT JOIN against your data to set the values for each date:

WITH years AS (

SELECT 2020 AS year UNION SELECT 2021 AS year UNION SELECT 2023 AS year UNION SELECT 2024 AS year

),

months AS (

SELECT 1 AS month UNION SELECT 2 AS month UNION SELECT 3 AS month UNION SELECT 4 AS month UNION

SELECT 5 AS month UNION SELECT 6 AS month UNION SELECT 7 AS month UNION SELECT 8 AS month UNION

SELECT 9 AS month UNION SELECT 10 AS month UNION SELECT 11 AS month UNION SELECT 12 AS month

),

days AS (

SELECT 1 AS day UNION SELECT 2 AS day UNION SELECT 3 AS day UNION SELECT 4 AS day UNION SELECT 5 AS day UNION

SELECT 6 AS day UNION SELECT 7 AS day UNION SELECT 8 AS day UNION SELECT 9 AS day UNION SELECT 10 AS day UNION

SELECT 11 AS day UNION SELECT 12 AS day UNION SELECT 13 AS day UNION SELECT 14 AS day UNION SELECT 15 AS day UNION

SELECT 16 AS day UNION SELECT 17 AS day UNION SELECT 18 AS day UNION SELECT 19 AS day UNION SELECT 20 AS day UNION

SELECT 21 AS day UNION SELECT 22 AS day UNION SELECT 23 AS day UNION SELECT 24 AS day UNION SELECT 25 AS day UNION

SELECT 26 AS day UNION SELECT 27 AS day UNION SELECT 28 AS day UNION SELECT 29 AS day UNION SELECT 30 AS day UNION

SELECT 31 AS day

),

all_dates AS (

SELECT DATE(STR_TO_DATE(CONCAT(years.year, '-', months.month, '-', days.day), '%Y-%m-%d')) AS day

FROM years

CROSS JOIN months

CROSS JOIN days

WHERE DATE(STR_TO_DATE(CONCAT(years.year, '-', months.month, '-', days.day), '%Y-%m-%d')) IS NOT NULL

)

SELECT day

FROM all_dates

WHERE day BETWEEN '2021-04-30' AND '2024-10-23'

ORDER BY day

GrantSmith

Using magic ETL, take the date dimension dataset (Domo Dimensions connector) and feed it into a Add Constant tile to create a new field called "join column" with a value of 1. Do the same for your input dataset. Feed both of those into the join based on the join column. Then you feed that into a filter field with your BETWEEN clause. The joining on the join column will do a cartesian join and add all dates for every record then you're filtering out the rows where the dates from the dimension dataset aren't within your range.

ArborRose

@hanmari - that second line should be:
SELECT 2020 AS year UNION SELECT 2021 AS year UNION SELECT 2022 AS year UNION SELECT 2023 AS year UNION SELECT 2024 AS year