Faiz Faiz - 29 days ago 7
SQL Question

showing all dates in a date range

I currently have a script returning 3 columns:

Code, Date, CountRows (see image below):

enter image description here

Each key has a start and end date. I want all dates to be returnes regardless if the countRows is 0 because there is no data for that date. i.e. Row 10 and 11 is missing 2 dates in between.

Answer

Create a calendar table and left join to your data. In this example I'm creating a temporary calendar table, but for performance reasons and for re-usability, I'd suggest creating a permanent one.

declare @calendar table (someDate date);
declare @x date = '11/1/2015';

while @x <= '12/1/2015' begin
    insert into @calendar values (@x);
    set @x = dateadd(d, 1, @x);
end;

--generate some sample data
declare @facts table (someDate date);
insert into @facts values ('11/1/2015'), ('11/1/2015'), ('11/10/2015'), ('11/20/2015'), ('11/21/2015'), ('11/5/2015'), ('11/9/2015');

select
    cal.someDate,
    CountRows = count(f.SomeDate)
from
    @calendar cal
    left join @facts f on cal.someDate = f.someDate
group by
    cal.someDate
order by
    cal.someDate;

The answer to this question has some good suggestions for creating a calendar table:

How to create a Calender table for 100 years in Sql

Comments