How to SELECT missing dates within range?

Question

Problem:

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

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:

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.

https://stackoverflow.com/questions/28285754/find-all-in-dates-range — iacob, Mar 21 '20 at 14:35

score 1 · Accepted Answer · edited May 23 '17 at 12:06

1

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

edited May 23 '17 at 12:06

Community

1
1

answered Nov 03 '16 at 15:01

Ben Osborne

1,412
13
20

Thanks. The link was very useful – Faiz Nov 03 '16 at 15:08
Hey, so i made the table and tried to left join it see above in question. I have edited it at end – Faiz Nov 03 '16 at 16:02
If you select statement you're selecting * from x, but the date will be in c. So, do something like: select c.date, x.* – Ben Osborne Nov 03 '16 at 16:12
I meant to start that comment, "In your.." rather than "If you..." – Ben Osborne Nov 03 '16 at 16:24
If Calendar contains all of your dates and you're not filtering it by an inner join or by where conditions, every record in Calendar _should_ be included in your results. I think you'll need to group by c.CalendarDate and aggregate the fields in X that you want to see. Try ordering by c.CalendarDate as well, so you can more easily see which dates have no corresponding records in X. – Ben Osborne Nov 03 '16 at 18:11
just saw your reply now. But in the meantime i managed to get it working by doing an inner join to the keyStart and end date then doing the left join to the orderdate table. – Faiz Nov 04 '16 at 10:10

How to SELECT missing dates within range?

Problem:

My Attempt:

1 Answers1