Show all months in date range even when no results are present in select

Question

I have the below query with a date range between '09/01/16' and '12/30/16'. In the attached picture results are only for September and October, I would like to still show November and December (or to the given date2 range) with a count of 0

How can I accomplish this?

declare @date1 date = '09/01/16'
declare @date2 date = '12/30/16'

select 
    count(distinct w.wkid) as WksCount
    ,DATENAME(MONTH, r.date) as [month]
    ,year(r.date) as [year]
from
    Workshop w
    left join Reservation r on r.wkid=w.wkid
    left join Registration reg on reg.wkid=w.wkid
where
    r.date between @date1 and @date2
group by
    DatePart(Month, r.date)
    ,DateName(Month, r.date)
    ,year(r.date)
order by
    year(r.date)
    ,DatePart(Month, r.date)

I

You need to have a calendar table as the base table of your query. You can easily derive one using a tally table or a create an actual calendar table. Then do a left join to Workshop and the rest of your query. — Sean Lange, Oct 19 '16 at 20:26

iamdave · Answer 1 · 2016-10-20T18:48:07.573

1

In a slightly different - and I think easier to read and understand - approach, you can build your Dates table with a CTE and join from that to get your full list of months.

I have specified the Month Start and the Month End in my cte so that you can join from it without having to resort to functions in your on criteria which can be a performance hit if your Workshop table is large as the functions will prohibit index utilisation.

If your r.date field is a datetime, you will need to adjust the below to handle values up to the last millisecond of the last day of the month:

declare @date1 date = '20160801';
declare @date2 date = '20161231';

with Dates
as
(
    select dateadd(m,datediff(m,0,@date1),0) as MonthStart
            ,dateadd(d,-1,dateadd(m,datediff(m,0,@date1)+1,0)) as MonthEnd

    union all

    select dateadd(m,1,MonthStart)
            ,dateadd(d,-1,dateadd(m,2,MonthStart))
    from Dates
    where MonthStart < dateadd(m,datediff(m,0,@date2),0)
)
select 
    count(distinct w.wkid) as WksCount
    ,DATENAME(MONTH, d.MonthStart) as [month]
    ,year(d.MonthStart) as [year]
from Dates d
    left join Reservation r on r.date between d.MonthStart and d.MonthEnd
        and r.date between @date1 and @date2
    left join Workshop w on r.wkid=w.wkid
    left join Registration reg on reg.wkid=w.wkid
group by
    DatePart(Month, r.date)
    ,DateName(Month, r.date)
    ,year(r.date)
order by
    year(r.date)
    ,DatePart(Month, r.date);

edited Oct 20 '16 at 18:48

answered Oct 20 '16 at 11:02

iamdave

12,023
3
24
53

this query runs but does not give me November and December in the result set – esausilva Oct 20 '16 at 16:43
@JGeZau Apologies, without your schema I am unable to test, but my edited answer should work. – iamdave Oct 20 '16 at 18:49
Yeah, I understand that without the schema, is kinda difficult to test sql. I tested the updated query and still does not give me November/December :/ – esausilva Oct 20 '16 at 20:14
@JGeZau Have you actually tried understanding my script and working out what is not working for you on your end? – iamdave Oct 21 '16 at 08:49
Yes, I have, but I am not really savvy when it comes to SQL. I spent some time trying to make it work on my end, and I actually like the how your CTE gives me all the months (start to end) in my date range...CTE is a new concept for me also, but anyways, thanks for your response :) – esausilva Oct 21 '16 at 14:36
Good job on avoiding the function in the join. I didn't bother trying to remove it. Also, I smiled when I saw in your edit that we omitted the same condition in our original answer. ("and r.date between @date1 and @date2"). ☺ – AXMIM Oct 24 '16 at 20:44

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Here is the jist of what Sean Lange suggested.

1: A tally table is first built.

2: Then, the tally table is used with left join to fetch all month between dates and you data linked to proper month. (Min and max are included in my code sample)

So month without data will only have one record with null in every column except the month number. Now to have the month name instead of the month number, see this question to know how.

P.S : The month number is Tally.N

declare @date1 date = '09/01/16'
declare @date2 date = '12/30/16'

--1: Build a tally table with CTE
;WITH
N0(_)            AS (SELECT NULL UNION ALL SELECT NULL),
N1(_)            AS (SELECT NULL FROM N0 AS L CROSS JOIN N0 AS R),
Tally            AS (SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))  FROM N1 AS L CROSS JOIN N1 AS R)

--2: Select data using the tally
select 
    count(distinct w.wkid) as WksCount
    ,Tally.N as [monthNo]
from Tally,
LEFT JOIN Reservation   AS r    ON Tally.N = DATEPART(MONTH, r.date)
                                AND r.date >= @date1
                                AND r.daye <= @date2
LEFT JOIN Workshop      AS w    ON r.wkid = w.wkid
left join Registration  AS reg  ON w.wkid = reg.wkid
WHERE Tally.N >= DATEPART(MONTH, @date1)
AND  Tally.N <= DATEPART(MONTH, @date2)
GROUP BY
    Tally.N --N is the month number
ORDER BY
    Tally.N

I had to change this one a little bit `LEFT JOIN Reservation AS r ON r.begintime between @date1 and @date2` but even with this change, the `WksCount` is not correct. Strange :/ — esausilva, Oct 20 '16 at 16:46
@JGeZau Yes, indeed my answer was incomplete. I added the missing condition in the joins in my answer as well as the missing "group by". Notice that I added your condition and not replaced. The original one need to stay there to link the reservations to the right month number. — AXMIM, Oct 20 '16 at 18:41

olk · Accepted Answer · 2016-10-20T10:36:44.657

For not very big result tables using a table variable as a "base table" much easier:

--add these lines before your select
declare @d datetime set @d=@date1
declare @t table(count int, month varchar(12), year int)
--fill the table with 0-month-year values
while @d < @date2
begin
    insert into @t values(0, datename(mm, @d), datepart(yy, @d))
    set @d = dateadd(mm, 1, @d)
end

then add your results to the table variable:

insert into @t 
select count(distinct w.wkid)
    ,DATENAME(MONTH, r.date)
    ,year(r.date)
from Workshop w
    left join Reservation r on r.wkid=w.wkid
    left join Registration reg on reg.wkid=w.wkid
where r.date between @date1 and @date2
group by DatePart(Month, r.date) ,DateName(Month, r.date), year(r.date)

and finally get required result:

select sum(count) as count, month, year from @t
    group by year, month
    order by year desc, month desc

Aggregate query from relatively little table variable is much more cheap then one more LEFT JOIN. If you have a lot of periods, consider using of #temporary table.

Sorry, I haven't MSSqlServer installed right now to check my code, but the idea is quite simple.

I present to you [SQL Fiddle](http://sqlfiddle.com/#!6) in which you can test/run SQL snippets for free (donate-when-happy model). Supports various flavors of RDBMS's: mysql, orcale, sql server .... — TT., Oct 20 '16 at 19:02
This works and meets my needs. Had to add extra column `monthNbr int` to table variable and `datepart(mm, @d)` to the insert into `@t` to be able to sort by `year, monthNbr`, otherwise it was sorting alphabetically by month. Thanks — esausilva, Oct 20 '16 at 20:15

Show all months in date range even when no results are present in select

3 Answers3

1: A tally table is first built.

2: Then, the tally table is used with left join to fetch all month between dates and you data linked to proper month. (Min and max are included in my code sample)