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?
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)