dwain.c (10/9/2013)
Luis Cazares (10/9/2013)
You need a Units table or at least a Units set to look for all units. after that the rest is easy, just add a RIGHT JOIN.
SELECT
count(fr.[Unit]) as Actual,
fr.unit,
[1st_of_month],
last_of_month
FROM [lchs_live].[dbo].[Full_Referrals_Dataset_live] fr
RIGHT JOIN Units u ON fr.unit = u.unit
where RfR1 = '18 month project'
group by fr.unit,
[1st_of_month],
[last_of_month]
Uh, Luis. Isn't that RIGHT JOIN going to leave you with NULLs in the resulting [1st_of_month], last_of_month columns? And also I think you'd want to refer to u.unit and not fr.unit.
You're absolutely right, I didn't think the query throughly and got distracted. :hehe: