Joining and getting all dates between start and end date

TMonty0319
TMonty0319 Member
in Connectors

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

Tagged:

Best Answers

  • hanmari
    hanmari Member
    Answer ✓

    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
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • hanmari
    hanmari Member
    Answer ✓

    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
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ArborRose
    ArborRose Coach

    @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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories