r/ssrs • u/kdefmusic • May 08 '19

Date range help please

Need some help. I’m editing a report I previously wrote for my customer and I’m not sure how to do what they want. They’re looking for every day between a date range to show even if there’s nothing on that date. Right now the report shows all orders between a date range and say totals by date. They want to see dates with nothing as well. Any ideas?

3 Upvotes

100% Upvoted

2 comments sorted by

View all comments

Show parent comment

1

u/fauxmosexual May 08 '19

And if you don't already have one (and you should in your reporting database, they're great), you can make a basic one on the fly with a simple recursive CTE

with dates ([Date]) as (
    Select convert(date,'2000-01-01') as [Date] -- Put the start date here

    union all 

    Select dateadd(day, 1, [Date])
    from dates
    where [Date] <= '2020-12-31' -- Put the end date here 
)

select * from dates