CODEWITHSUNDEEP
sqlsql-server
Faiz
Faiz

Reputation: 283

How to SELECT missing dates within range?

Problem:

I currently have a script returning 3 columns (key, date, CountRows):

enter image description here

Each key has a start and end date. I want all dates to be returned regardless if the countRows is 0 because there is no data for that date e.g. there are two missing dates between rows 10 and 11.

My Attempt:

I wrote a left join like:

SELECT c.calendarDate, x.*
FROM   Calendar c
LEFT JOIN (SELECT key, 
                  orderDate, 
                  keyStartDate, 
                  keyEndDate, 
                  count(*) 
           FROM multiple tables
           GROUP BY ...) x 
ON  c.date >= x.startDdate 
AND c.date <  DATEADD(DD,1,x.endDate)

Output:

enter image description here

However no rows are returned for dates in the range with no orders. I wish to return all such dates on orderDate with a count(*) of 0.

Upvotes: 0

Views: 106

Answers (1)

Ben Osborne
Ben Osborne

Reputation: 1552

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

Upvotes: 1

Related Questions