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?
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)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With