quote:

---

Originally posted by jamesingamells

Hi, I have posted a similar query before, but I now have the proper tables built and again need some assistance. I have the below query. In the base table there are approx 30 [event_done_at] fields and what i require is a line for each one and populated with 0 if there are no records as currently i am only getting lines when there is data in the [patient count] field. So for each month i would like 30 lines.

SELECT
'CQN12-5i1as [Measure]
,'CQUIN' as [Source]
,[Event_done_at] as [Objective]
,sum([Patient Count]) as [Actual]
,[1st_of_month]
,[last_of_month]


FROM [lchs_analysis].[dbo].MECC
where [Read_code] = '(XaIkW)'

group by [Event_done_at]
,[1st_of_month]
,[last_of_month]

---

SELECT 
	[CQN12-5i1as] [Measure]
	,'CQUIN' as [Source]
	,a.[Event_done_at] as [Objective]
	,sum([Patient Count]) as [Actual]
	,[1st_of_month]
	,[last_of_month]

FROM 
	(select distinct  [event_done_at] from [lchs_analysis].[dbo].MECC) as a
	left join [lchs_analysis].[dbo].MECC b on a.[event_done_at] = b.[event_done_at]
where [Read_code] = '(XaIkW)'

group by a.[Event_done_at]
,[1st_of_month]
,[last_of_month]