display row even if categories case count = 0

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

SQL Server 2008 Forums

Transact-SQL (2008)

display row even if categories case count = 0

Author

Topic

DKV
Starting Member

5 Posts

Posted - 2010-08-03 : 11:51:33

I am running this sql quer to get case counts by category per month. This displays a row if case count > 0 for any category for that month. What do I do for it to display a row even if case count = 0 for any category.

SELECT
CASE AA.DOS.SpecimenType.SpecimenTypeName WHEN 'Bone Marrow' THEN 'BM' WHEN 'Peripheral Blood' THEN 'PB' WHEN 'Leukemic Blood' THEN 'LB' WHEN
'Products of Conception' THEN 'POC' ELSE 'Tissue' END AS Spec,
((convert(varchar, (DateName(month,AA.ORDERS.Specimen.ReceivedDate)),1 ))+' '+(convert(varchar,year(AA.ORDERS.Specimen.ReceivedDate),1)))as Mdate,
Year(AA.ORDERS.Specimen.ReceivedDate) as yyear,
month(AA.ORDERS.Specimen.ReceivedDate)as mmonth,
CC.CytoStats.Case_Details.Cat1,
CC.CytoStats.Case_Details.Cat3,
COUNT (*) as CaseCount,
TotTab.CaseCount as total
FROM AA.ORDERS.ComponentOrder INNER JOIN
AA.ORDERS.ApCase INNER JOIN
AA.ORDERS.TestOrder ON AA.ORDERS.ApCase.CaseID = AA.ORDERS.TestOrder.CaseID INNER JOIN
AA.ORDERS.TestOrderSpecimen ON AA.ORDERS.TestOrder.TestOrderID = AA.ORDERS.TestOrderSpecimen.TestOrderID INNER JOIN
AA.ORDERS.Specimen ON AA.ORDERS.TestOrderSpecimen.SpecimenID = AA.ORDERS.Specimen.SpecimenID ON
AA.ORDERS.ComponentOrder.TestOrderID = AA.ORDERS.TestOrder.TestOrderID INNER JOIN
AA.DOS.SpecimenType ON AA.ORDERS.Specimen.SpecimenTypeID = AA.DOS.SpecimenType.SpecimenTypeID
Join CC.CytoStats.Case_Details on AA.ORDERS.ApCase.CaseNo = CC.CytoStats.Case_Details.CaseNo
join @CaseCounttable as TotTab on Year(AA.ORDERS.Specimen.ReceivedDate) = TotTab.yyear and Month(AA.ORDERS.Specimen.ReceivedDate) = TotTab.mmonth

WHERE AA.ORDERS.ComponentOrder.ComponentID = 100129

group by AA.DOS.SpecimenType.SpecimenTypeName,
AA.ORDERS.Specimen.ReceivedDate,
CC.CytoStats.Case_Details.Cat1,
CC.CytoStats.Case_Details.Cat3,
TotTab.caseCount
order by
Year(AA.ORDERS.Specimen.ReceivedDate) desc,
month(AA.ORDERS.Specimen.ReceivedDate)desc

D

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-03 : 12:03:03

isnull(count(*),0) as CaseCount

Jim

Everyday I learn something that somebody else already knew

DKV
Starting Member

5 Posts

Posted - 2010-08-03 : 12:25:25

quote:
Originally posted by jimf

isnull(count(*),0) as CaseCount

Jim

Everyday I learn something that somebody else already knew

To test this I deleted rows with cat1 = 'Canceled' from CC.CytoStats.Case_Details table, and changed my query to the following, but i still don't see a row for 'Cancelled' cases with case count = 0.

SELECT
CASE AA.DOS.SpecimenType.SpecimenTypeName WHEN 'Bone Marrow' THEN 'BM' WHEN 'Peripheral Blood' THEN 'PB' WHEN 'Leukemic Blood' THEN 'LB' WHEN
'Products of Conception' THEN 'POC' ELSE 'Tissue' END AS Spec,
((convert(varchar, (DateName(month,AA.ORDERS.Specimen.ReceivedDate)),1 ))+' '+(convert(varchar,year(AA.ORDERS.Specimen.ReceivedDate),1)))as Mdate,
Year(AA.ORDERS.Specimen.ReceivedDate) as yyear,
month(AA.ORDERS.Specimen.ReceivedDate)as mmonth,
CC.CytoStats.Case_Details.Cat1,
CC.CytoStats.Case_Details.Cat3,
isnull(count(*),0) as CaseCount--,
--TotTab.CaseCount as total
FROM AA.ORDERS.ComponentOrder INNER JOIN
AA.ORDERS.ApCase INNER JOIN
AA.ORDERS.TestOrder ON AA.ORDERS.ApCase.CaseID = AA.ORDERS.TestOrder.CaseID INNER JOIN
AA.ORDERS.TestOrderSpecimen ON AA.ORDERS.TestOrder.TestOrderID = AA.ORDERS.TestOrderSpecimen.TestOrderID INNER JOIN
AA.ORDERS.Specimen ON AA.ORDERS.TestOrderSpecimen.SpecimenID = AA.ORDERS.Specimen.SpecimenID ON
AA.ORDERS.ComponentOrder.TestOrderID = AA.ORDERS.TestOrder.TestOrderID INNER JOIN
AA.DOS.SpecimenType ON AA.ORDERS.Specimen.SpecimenTypeID = AA.DOS.SpecimenType.SpecimenTypeID
Join CC.CytoStats.Case_Details on AA.ORDERS.ApCase.CaseNo = CC.CytoStats.Case_Details.CaseNo
--join @CaseCounttable as TotTab on Year(AA.ORDERS.Specimen.ReceivedDate) = TotTab.yyear and Month(AA.ORDERS.Specimen.ReceivedDate) = TotTab.mmonth

WHERE AA.ORDERS.ComponentOrder.ComponentID = 100129

group by AA.DOS.SpecimenType.SpecimenTypeName,
AA.ORDERS.Specimen.ReceivedDate,
CC.CytoStats.Case_Details.Cat1,
CC.CytoStats.Case_Details.Cat3--,
--TotTab.caseCount
order by
Year(AA.ORDERS.Specimen.ReceivedDate) desc,
month(AA.ORDERS.Specimen.ReceivedDate)desc

D

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-03 : 13:23:00

If a row doesn't exist then you are not going to get a row with a zero back. You'll need to do some sort of OUTER join in order to get that value.

DKV
Starting Member

5 Posts

Posted - 2010-08-03 : 13:45:46

quote:
Originally posted by Lamprey

If a row doesn't exist then you are not going to get a row with a zero back. You'll need to do some sort of OUTER join in order to get that value.

for testing I did create temp categories, cat1 and cat3 tables and did a left outer join, but I still don't see rows for cat3='Canceled'. Also changed Count(*) to count(xxx). Please check my query:

Declare @Categories Table
(
Categories varchar(50)
)
insert into @Categories values('BM')
insert into @Categories values('LB')
insert into @Categories values('Tissue')
insert into @Categories values('PB')
insert into @Categories values('POC')

Declare @Cat1 Table
(
Cat1 varchar(50)
)
insert into @Cat1 values('Abnormal')
insert into @Cat1 values('Normal')
insert into @Cat1 values('Failure')
insert into @Cat1 values('QNS')
insert into @Cat1 values('Canceled')

Declare @Cat3 Table
(
Cat3 varchar(50)
)
insert into @Cat3 values('SubOptimal')
insert into @Cat3 values('Optimal')

SELECT
CASE AA.DOS.SpecimenType.SpecimenTypeName WHEN 'Bone Marrow' THEN 'BM' WHEN 'Peripheral Blood' THEN 'PB' WHEN 'Leukemic Blood' THEN 'LB' WHEN
'Products of Conception' THEN 'POC' ELSE 'Tissue' END AS Spec,
((convert(varchar, (DateName(month,AA.ORDERS.Specimen.ReceivedDate)),1 ))+' '+(convert(varchar,year(AA.ORDERS.Specimen.ReceivedDate),1)))as Mdate,
Year(AA.ORDERS.Specimen.ReceivedDate) as yyear,
month(AA.ORDERS.Specimen.ReceivedDate)as mmonth,
CC.CytoStats.Case_Details.Cat1,
CC.CytoStats.Case_Details.Cat3,
isnull(count(CC.CytoStats.Case_Details.Cat3),0) as CaseCount

FROM AA.ORDERS.ComponentOrder INNER JOIN
AA.ORDERS.ApCase INNER JOIN
AA.ORDERS.TestOrder ON AA.ORDERS.ApCase.CaseID = AA.ORDERS.TestOrder.CaseID INNER JOIN
AA.ORDERS.TestOrderSpecimen ON AA.ORDERS.TestOrder.TestOrderID = AA.ORDERS.TestOrderSpecimen.TestOrderID INNER JOIN
AA.ORDERS.Specimen ON AA.ORDERS.TestOrderSpecimen.SpecimenID = AA.ORDERS.Specimen.SpecimenID ON
AA.ORDERS.ComponentOrder.TestOrderID = AA.ORDERS.TestOrder.TestOrderID INNER JOIN
AA.DOS.SpecimenType ON AA.ORDERS.Specimen.SpecimenTypeID = AA.DOS.SpecimenType.SpecimenTypeID
Join CC.CytoStats.Case_Details on AA.ORDERS.ApCase.CaseNo = CC.CytoStats.Case_Details.CaseNo
Left Outer Join @Categories as Cat ON AA.DOS.SpecimenType.SpecimenTypeName = Cat.Categories
Left Outer Join @Cat1 as Cat1 ON CC.CytoStats.Case_Details.Cat1 = Cat1.Cat1
Left Outer Join @Cat3 as Cat3 ON CC.CytoStats.Case_Details.Cat3 = Cat3.Cat3

WHERE AA.ORDERS.ComponentOrder.ComponentID = 100129

group by AA.DOS.SpecimenType.SpecimenTypeName,
AA.ORDERS.Specimen.ReceivedDate,
CC.CytoStats.Case_Details.Cat1,
CC.CytoStats.Case_Details.Cat3
order by
Year(AA.ORDERS.Specimen.ReceivedDate) desc,
month(AA.ORDERS.Specimen.ReceivedDate)desc

D

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-03 : 14:26:29

The way you've writting query I think you'll have to RIGHT OUTER JOIN or move the LEFT OUTER JOINs above the INNER JOINs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources