Adding Zeros

Question

Adding Zeros

james.ingamells

SSC Veteran

Points: 294
More actions
November 8, 2013 at 2:20 am

#282231

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]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

james.ingamells (11/8/2013)
Hi, I have posted a similar query before, but I now have the proper tables built...

Sorry, I can't see them. I took a look at your previous posts, but I can't find the definition script. Can you post it again please?

-- Gianluca Sartori

james.ingamells SSC Veteran Points: 294 More actions · Answer 2

My previous posts no longer correlate to what i am trying to achieve now, but the concept is the same. I have a calendar table and also a table with all the objectives listed as that was mentioned as being required.

James

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

james.ingamells (11/8/2013)
My previous posts no longer correlate to what i am trying to achieve now, but the concept is the same. I have a calendar table and also a table with all the objectives listed as that was mentioned as being required.
James

Great, just post the definition here, so that we can try to help you.

Read the first article linked in my signature line if you don't know how to do it.

-- Gianluca Sartori

james.ingamells SSC Veteran Points: 294 More actions · Answer 4

Hi, here goes at posting the definition then. Apologies if its not what is required, not posted defintion before.

Current View

CREATE view [dbo].[CQN12-5i1_Signposts_Smoking_Cessation]

as

SELECT

'CQN12-5i1_Signposts_Smoking_Cessation as [Measure] (Varchar(37),not null)

,'CQUIN_data' as [Source] (varchar(10),not null)

,[Event_done_at] as [Objective] (varchar,(255,not null)

,sum([Patient Count]) as [Actual](bigint,null)

,[1st_of_month] (datetime,null)

,[last_of_month] (datetime,null)

FROM [lchs_analysis].[dbo].MECC

where [Read_code] = '(XaIkW)'

group by [Event_done_at]

,[1st_of_month]

,[last_of_month]

Insert into

SELECT'CQN12-5i1_Signposts_Smoking_Cessation','CQUIN_data','Adult Countywide Services','8','8','Sep 30 2013 12:00AM'UNION ALL

SELECT'CQN12-5i1_Signposts_Smoking_Cessation','CQUIN_data','Adult Countywide Services','4','4','Oct 31 2013 12:00AM'UNION ALL

SELECT'CQN12-5i1_Signposts_Smoking_Cessation','CQUIN_data','HV North West','1','1','Oct 31 2013 12:00AM'UNION ALL

SELECT'CQN12-5i1_Signposts_Smoking_Cessation','CQUIN_data','HV South East','1','1','Sep 30 2013 12:00AM'UNION ALL

SELECT'CQN12-5i1_Signposts_Smoking_Cessation','CQUIN_data','HV South West','1','1','Sep 30 2013 12:00AM'UNION ALL

SELECT'CQN12-5i1_Signposts_Smoking_Cessation','CQUIN_data','HV South West','1','1','Nov 30 2013 12:00AM'UNION ALL

SELECT'CQN12-5i1_Signposts_Smoking_Cessation','CQUIN_data','NE - Adult Integrated Services','2','2','Sep 30 2013 12:00AM'UNION ALL

SELECT'CQN12-5i1_Signposts_Smoking_Cessation','CQUIN_data','NE - Adult Integrated Services','3','3','Oct 31 2013 12:00AM'UNION ALL

SELECT'CQN12-5i1_Signposts_Smoking_Cessation','CQUIN_data','NW - Adult Integrated Services','1','1','Oct 31 2013 12:00AM'UNION ALL

SELECT'CQN12-5i1_Signposts_Smoking_Cessation','CQUIN_data','SE - Adult Integrated Services','1','1','Oct 31 2013 12:00AM'UNION ALL

SELECT'CQN12-5i1_Signposts_Smoking_Cessation','CQUIN_data','SE - Adult Integrated Services','1','1','Nov 30 2013 12:00AM'UNION ALL

SELECT'CQN12-5i1_Signposts_Smoking_Cessation','CQUIN_data','SW - Adult Integrated Services','1','1','Oct 31 2013 12:00AM'

What i had in mind was somehting like this, but dont know if i am on the right lines?

union all

select distinct

'CQN12-5i1_Signposts_Smoking_Cessation' as [Measure]

,'CQUIN_data' as [Source]

,[Event_done_at] as [Objective]

,'0' as actual

,[1st_of_month]

,[last_of_month]

from [lchs_analysis].[dbo].MECC M

left outer join [lchs_ref].[dbo].[S1_Units] u on

m.[Event_done_at]=U.Unit

group by

'CQN12-5i1_Signposts_Smoking_Cessation' as [Measure]

,'CQUIN_data' as [Source]

,[Event_done_at] as [Objective]

,[1st_of_month]

,[last_of_month]

Apologies if that is not right.