CODEWITHSUNDEEP
sql
ShubhamA
ShubhamA

Reputation: 322

Create data for all dates in a date range

Here is the input dataset I have with revenue for some of the days:

enter image description here

Would need an output with all dates between 03/01/2021 to 03/15/2021. $0 revenue where the value is not present

enter image description here

Upvotes: 0

Views: 957

Answers (2)

Bart Hofland
Bart Hofland

Reputation: 3905

You could use two additional helper tables:

  • a Dates table that holds all the dates in the desired range, and
  • a Zipcodes table that holds the distinct zip codes.

Instead of physical tables, temporary tables and/or table variables, you could also consider using table expressions (subqueries in the FROM-clause) or common table expressions (in a WITH-clause). Common table expressions can often also be recursive, which might be a nice solution for creating a value range with specific start and end values (like your dates range here).

It is already pointed out, that concrete solution proposals heavily depend on the target DBMS. Sadly, it is unspecified here (at the time of writing this answer). Below is a sample implementation for Microsoft SQL Server, using T as the placeholder for your actual table name. It uses two common table expressions: a recursive CTE for the Dates table and a normal CTE for the Zipcodes table.

WITH
  [Dates] AS (
    SELECT CAST('2020-03-01' AS DATE) AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM [Dates]
    WHERE [Date] < '2020-03-15'
  ),
  [ZipCodes] AS (
    SELECT DISTINCT [Zip] FROM T
  )
SELECT D.[Date], Z.[Zip], COALESCE(T.[Revenue], 0) AS [Revenue]
FROM
  [Dates] AS D
  CROSS JOIN [ZipCodes] AS Z
  LEFT JOIN T ON T.[Date] = D.[Date] AND T.[Zip] = Z.[Zip]
ORDER BY Z.[Zip], D.[Date]

Upvotes: 1

umberto-petrov
umberto-petrov

Reputation: 733

You haven't specified your RDBMS, it's probably going to be implemented differently in different systems. E.g. in the SQL Server there's a master.dbo.spt_values table, which can potentially be used for that:

SELECT DATEADD(day, d.number, '2021-03-01') as Date,
  zips.Zip,
  IsNull(t.Revenue, 0) as Revenue
FROM master.dbo.spt_values d
  CROSS JOIN (SELECT DISTINCT(Zip) FROM Table) zips
  LEFT JOIN Table t
    ON DATEADD(day, d.number, '2021-03-01') = t.Date
    AND zips.Zip = t.Zip
WHERE d.type = 'P'
  AND d.number < 15
ORDER BY zips.Zip, d.number

It's somewhat of a hack though

Upvotes: 0

Related Questions