How to show 0 count for missing object on left outer join
I have a list of objects that look something like this:
Facilities
--------------------------------------------------
| ID | NAME | ABBREVIATION |
--------------------------------------------------
| 2 | Adams | ACCC |
--------------------------------------------------
| 3 | Bucks | BCJS |
--------------------------------------------------
| 4 | Rocking | RCDOC |
--------------------------------------------------
consultations
---------------------------------------
| CREATION_DATE | NAME |
---------------------------------------
| 2014-01-01 | ACCC |
---------------------------------------
| 2014-01-02 | ACCC |
---------------------------------------
| 2014-01-01 | BCJS |
---------------------------------------
| 2014-02-01 | RCDOC |
---------------------------------------
| 2014-02-03 | BCJS |
---------------------------------------
My desired results should look like this:
-----------------------------------------------------
| FACILITY_ID | MONTH | COUNT |
-----------------------------------------------------
| ACCC | 1 | 2 |
-----------------------------------------------------
| BCJS | 1 | 1 |
-----------------------------------------------------
| RCODOC | 1 | 0 |
-----------------------------------------------------
| ACCC | 2 | 0 |
-----------------------------------------------------
| BCJS | 2 | 1 |
-----------------------------------------------------
| RCODOC | 2 | 1 |
-----------------------------------------------------
My current request looks like this:
SELECT
c.facility_id,
DATEPART(MONTH,creation_date),
COUNT(1) as count
FROM
consultations c
left outer join facilities f on c.facility_id = f.abbreviation
WHERE
DATEPART(YEAR, creation_date) = '2014'
GROUP BY
c.facility_id,
DATEPART(MONTH,creation_date)
ORDER BY
DATEPART(MONTH,creation_date),
c.facility_id
With this current request, I am not refunding count
for RCDOC
a month.
source to share
This should do the trick.
DECLARE @FACILITIES TABLE (FACILITY_ID INT, NAME VARCHAR(50), ABBREVIATION VARCHAR(50))
DECLARE @CONSULTATIONS TABLE (CREATION_DATE DATETIME, NAME VARCHAR(50))
INSERT INTO @FACILITIES
SELECT 2,'Adams','ACCC' UNION
SELECT 3,'Bucks','BCJS' UNION
SELECT 4,'Rocking','RCDOC'
INSERT INTO @CONSULTATIONS
SELECT '2014-01-01','ACCC' UNION
SELECT '2014-01-02','ACCC' UNION
SELECT '2014-01-01','BCJS' UNION
SELECT '2014-02-01','RCDOC' UNION
SELECT '2014-02-03','BCJS'
SELECT F.ABBREVIATION, X.MOS, COUNT(C.CREATION_DATE) AS NUM
FROM @FACILITIES F
CROSS JOIN (SELECT DISTINCT MONTH(CREATION_DATE) MOS FROM @CONSULTATIONS WHERE YEAR(CREATION_DATE) = 2014) AS X
LEFT OUTER JOIN @CONSULTATIONS C
ON F.ABBREVIATION = C.NAME
AND MONTH(C.CREATION_DATE) = X.MOS
GROUP BY F.ABBREVIATION, X.MOS
ORDER BY X.MOS, F.ABBREVIATION
source to share
You need to start with a list of all objects and months. Then return the data you want. You get the complete list using cross join
:
SELECT f.facility_id, m.mon,
COUNT(c.facility_id) as count
FROM facilities f cross join
(select distinct month(creation_date) as mon
from consultations
where year(creation_date) = 2014
) m left join
consultations c
on c.facility_id = f.abbreviation and month(c.creation_date) = m.mon
GROUP BY f.facility_id, m.mon
ORDER BY m.mon, f.facility_id;
I don't understand if there should c.facility_id
be c.name
, sample data has it one way, sample query another.
source to share