display row even if categories case count = 0

[ptd1]

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

 

[r937]

What do I do for it to display a row even if case count = 0 for any category

use a LEFT OUTER JOIN instead, and change COUNT(*) to COUNT(xxxxx) where xxxxx is the name of join column


i'd show you, but that query is too messy for me, and i don't even see a category table...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ptd1]

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

 

[markros]

If you want to see all categories, then you need to RIGHT JOIN to them instead of LEFT JOIN. In addition, you will need to move where clause into JOIN condition. This will become a bit complex, so may be you'd like to divide it in steps using CTE and start from the categories first.

PluralSight Learning Library