CODEWITHSUNDEEP
sqlsql-serverdatetimerecursive-querysql-server-2017
steenbergh
steenbergh

Reputation: 1771

How can I create a dates-table inside a query in SQL Server?

Say I want to match records in table_a that have a startdate and an enddate to individual days and see if on, for instance March 13, one or more records in table_a match. I'd like to solve this by generating a row per day, with the date as the leading column, and any matching data from table_a as a left join.

I've worked with data warehouses that have date dimensions that make this job easy. But unfortunately I need to run this particular query on an OLTP database that doesn't have such a table.

How can I generate a row-per-day table in SQL Server? How can I do this inside my query, without temp tables, functions/procedures etc?

Upvotes: 0

Views: 210

Answers (2)

GMB
GMB

Reputation: 222582

An alternative is a recursive query to generate the date series. Based on your pseudo-code:

with dates_table as (
    select <your-start-date> dt
    union all
    select dateadd(day, 1, dt) from dates_table where dt < <your-end-date>
)
select d.dt, a.<whatever>
from dates_table d
left outer join table_a a on <join / date matching here>
-- where etc etc
option (maxrecursion 0)

Upvotes: 2

steenbergh
steenbergh

Reputation: 1771

I found a bit of a hack way to do this. I'll assume two years of dates is sufficient for your dates table.

Now, find a table in your database that has at least 800 records (365 x 2 + leap years - headache for multiplying 365 = ~~ 800). The question talks about selecting data from table_a, so we'll assume this is table_b. Then, create this Common Table Expression at the top of the query:

with dates_table as (
    select top 800  -- or more/less if your timespan isn't ~2years
        [date] = date_add(day, ROW_NUMBER() over (order by <random column>) -1, <your-start-date>)
    from table_b
)

select d.[date]
, a.<whatever>
from dates_table d
left outer join table_a a on <join / date matching here>
-- where etc, etc, etc

Some notes:

  • This works by just getting the numbers 0 - 799 and adding them to an arbitrary date.
  • If you need more or less dates than two years, increase or decrease the number fed into the top statement. Ensure that table_b has sufficient rows: select count(*) from table_b.
  • <random column can be any column on table_b, the ordering doesn't matter. We're only interested in the numbers 1-800 ( -1 for a range of 0-799), but ROW_NUMBER requires an order by argument.
  • <your-start-date> has the first date tyou want in the dates table, and is included in the output.
  • in the where of the joined query, you could filter out any excess days that we overshot by taking 800 rows instead of 730 (+leap) by adding stuff like year(d.[date]) IN (2020, 2021).
  • If table_a has more than 800 records itself, this could be used as the basis for the dates_table instead of some other table too.

Upvotes: 0

Related Questions