Combining two queries together to form month count

Question

I currently have the following query:

select 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0) as MonthYear, count(*) as Count
from 
    visit
where 
    StartDate >= DATEADD(year,-1,GETDATE())
    and clientid = 142
group by  
    DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0)

which returns the following results:

+-------------------------+----+
| 2015-12-01 00:00:00.000 |  1 |
| 2016-02-01 00:00:00.000 | 13 |
| 2016-03-01 00:00:00.000 |  1 |
| 2016-04-01 00:00:00.000 | 22 |
| 2016-05-01 00:00:00.000 | 22 |
| 2016-06-01 00:00:00.000 | 25 |
| 2016-07-01 00:00:00.000 | 36 |
+-------------------------+----+

I also have the following query:

SELECT TOP (12) 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - test, 0) as        MonthYear
FROM
    test

which returns the following results:

+-------------------------+
| 2016-10-01 00:00:00.000 |
| 2016-09-01 00:00:00.000 |
| 2016-08-01 00:00:00.000 |
| 2016-07-01 00:00:00.000 |
| 2016-06-01 00:00:00.000 |
| 2016-05-01 00:00:00.000 |
| 2016-04-01 00:00:00.000 |
| 2016-03-01 00:00:00.000 |
| 2016-02-01 00:00:00.000 |
| 2016-01-01 00:00:00.000 |
| 2015-12-01 00:00:00.000 |
| 2015-11-01 00:00:00.000 |
+-------------------------+

What I need to do is combine the two queries into one query and show 0 for those months that are missing from the original query.

Could someone help please?

Answer 1

Traditionally this is solved with a calendar table (eg this msdn answer), but in your case a derived table and outer join should work:

select DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0) as MonthYear,count(*) as Count
    from (SELECT TOP (12) DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-test, 0) as MonthYear
  from test) cal LEFT OUTER JOIN visit on (cal.MonthYear = visit.MonthYear)
    where StartDate >= DATEADD(year,-1,GETDATE())
    and clientid = 142
    group by  DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0)

Answer 2

This method uses CROSS APPLY with VALUES to create your Master Month List.

I recommend replacing GETDATE() with a variable if you can.

CREATE TABLE #Visit(StartDate DATE,clientid int)

INSERT INTO #Visit VALUES
('20160304',142)
,('20160305',142)
,('20160508',142)
,('20160612',142)
,('20160617',142)

SELECT 
    T.Mnth
    ,COUNT(V.StartDate) AS [Count]
FROM 
     (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) c (test)
        CROSS APPLY (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - c.test, 0) Mnth) T 
        LEFT JOIN #Visit V
            ON
            DATEDIFF(MM,V.startdate,T.Mnth) = 0
            AND StartDate >= DATEADD(year,-1,GETDATE())
            AND clientid = 142
GROUP BY
    T.Mnth
ORDER BY 
    T.Mnth DESC