SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Adding Zeros
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jamesingamells
Starting Member

11 Posts

Posted - 11/08/2013 :  04:18:54  Show Profile  Reply with Quote
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]

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 11/08/2013 :  08:27:07  Show Profile  Reply with Quote
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]



If you have a reference table that lists all the events_done_at values, left join to that table. If you don't have such a reference table, do the following:
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]
Go to Top of Page

jamesingamells
Starting Member

11 Posts

Posted - 11/08/2013 :  08:42:21  Show Profile  Reply with Quote
Hi James, Thank you for your suggestion. I have tried both options and neither appear to be working. I have a table with the event_done_at populated called S1_units. However its not populationg any zeros?

James
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/08/2013 :  11:10:02  Show Profile  Reply with Quote
show us how your table structure is and then explain how you want output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000