HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

HELP

Author

Topic

jhon11
Starting Member

42 Posts

Posted - 2007-12-18 : 14:25:49

I have one table on server....which has three column...
patientid, productcode and severitylevel

now in that table there are two types of productcode and
there are two types of severitylevel.

Now from that table i want to create one table which has four column and with four combination of ( 2 productcode* 2 severitylevel)

i wrote following query

declare @LoadInstanceStepId int
--declare @ReportPeriodEnd datetime
select @LoadInstanceStepId='56355'

create table #count
(
--reportingmonth datetime,
countAS smallint ,
countAI smallint ,
countCS smallint ,
countCI smallint
)

insert into #count (countAS)
select count(distinct patientid)
from reporting..drugDrugInteractionReport
where SeverityLevel=2 --special attnetion
and productcode=1 --adult
and LoadInstanceStepId=@LoadInstanceStepId

insert into #count (countAI)
select count(distinct patientid) from reporting..drugDrugInteractionReport
where SeverityLevel=1 --immedate attetion
and productcode=1--adult
and LoadInstanceStepId=@LoadInstanceStepId

insert into #count (countCS)
select count(distinct patientid) from reporting..drugDrugInteractionReport
where SeverityLevel=2
and productcode=6 --child
and LoadInstanceStepId=@LoadInstanceStepId

insert into #count (countCI)
select count(distinct patientid) from reporting..drugDrugInteractionReport
where SeverityLevel=1
and productcode=6 --child
and LoadInstanceStepId=@LoadInstanceStepId

select * from #count
drop table #count

but it gives me result like

countAS countAI countCS CountCI
120 null null null
null 0 null null
null null 0 null
null null null 0

I WANT FOLLOWING RESULT

countAS countAI countCS CountCI
120 0 0 0

looking fw for reply...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-18 : 14:33:08

Instead of select * from #count, run this:

SELECT SUM(COALESCE(countAS,0)) AS countAS, SUM(COALESCE(countAI,0)) AS countAI, SUM(COALESCE(countCS,0)) AS countCS, SUM(COALESCE(countCI,0)) AS countCI
FROM #count

We could probably also fix it by inserting the correct data into the temp table using only one insert statement, but I'm out of time right now.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-18 : 14:33:46

[code]INSERT #Count
(
CountAS,
CountAI,
CountCS,
CountCI
)
SELECT COUNT(DISTINCT CASE WHEN SecurityLevel = 2 AND ProductCode = 1 THEN PatientID ELSE NULL END) AS CountAS,
COUNT(DISTINCT CASE WHEN SecurityLevel = 1 AND ProductCode = 1 THEN PatientID ELSE NULL END) AS CountAI,
COUNT(DISTINCT CASE WHEN SecurityLevel = 2 AND ProductCode = 6 THEN PatientID ELSE NULL END) AS CountCS,
COUNT(DISTINCT CASE WHEN SecurityLevel = 1 AND ProductCode = 6 THEN PatientID ELSE NULL END) AS CountCI
FROM Reporting.DrugInteractionReport
WHERE LoadInstanceStepId = @LoadInstanceStepId[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources