Reputation: 283
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.
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.
Upvotes: 0
Views: 106
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