mayooran mayooran - 3 months ago 12
SQL Question

Conditional statement counts with CTE

I have the below table obtained as an output using the query provided below that.

enter image description here

Below is the query I used.

;WITH cte AS (
SELECT c.CaseID AS 'Case #',
m.ManufacturerName,
ou.OutcomeName
FROM Consumes con
INNER JOIN [Case] c
ON con.FKCaseID = c.CaseID
INNER JOIN Manufacturer m
ON m.ManufacturerID = con.FKManufacturerID
INNER JOIN Case_Outcome oc
ON oc.FKCaseID = c.CaseID
INNER JOIN OutCome ou
ON oc.FKOutcomeID = ou.OutcomeID
)

SELECT c.[Case #],
c.ManufacturerName,
STUFF((SELECT ','+OutcomeName
FROM cte
WHERE c.[Case #] = [Case #]
FOR XML PATH('')),1,1,'') as OutcomeName
FROM cte c
GROUP BY c.[Case #],c.ManufacturerNAme


I need to get the below output.

enter image description here

Here number of events is the count of case# for each manufacturer. Is there a way I can get the above output without using a CTE for each output in where condition? If so, please assist with an example.

I used the below query as posted down in the answer but its always 0% or 100% for outcome percentages. That is, if the outcomes of the case are all the same then this works fine.

SELECT
m.ManufacturerName,
COUNT(c.CaseID) AS '# Events',
COUNT(CASE WHEN ou.OutcomeName = 'Death' THEN c.CaseID ELSE NULL END) /COUNT(c.CaseID)*100.0 AS 'Death Events',
COUNT(CASE WHEN ou.OutcomeName = 'Hospitalization' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Hospitalization Events',
COUNT(CASE WHEN ou.OutcomeName = 'Life Threatening' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Life Threatening Events',
COUNT(CASE WHEN ou.OutcomeName = 'Disability' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Disability Events',
COUNT(CASE WHEN ou.OutcomeName = 'Congenital Anomaly' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Congenital Anomaly Events',
COUNT(CASE WHEN ou.OutcomeName = 'Required Intervention' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Required Intervention Events',
COUNT(CASE WHEN ou.OutcomeName = 'Other Serious' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Other Serious Events'
FROM Consumes con
INNER JOIN [Case] c ON con.FKCaseID = c.CaseID
INNER JOIN Manufacturer m ON m.ManufacturerID = con.FKManufacturerID
INNER JOIN Case_Outcome oc ON oc.FKCaseID = c.CaseID
INNER JOIN OutCome ou ON oc.FKOutcomeID = ou.OutcomeID
GROUP BY m.ManufacturerName


But when the outcomes are different it doesn't return the correct answer. Below is the count I get when for each case.

enter image description here

But my percentage result set looks like the following.

enter image description here

Answer

UPDATE: (fixed the issue with rounding - moved 100.00 in front of COUNT())

SELECT 
  m.ManufacturerName, 
  COUNT(DISTINCT c.CaseID),
  100.0 * COUNT(DISTINCT CASE WHEN ou.OutcomeName = 'Death' THEN c.CaseID ELSE NULL END) / COUNT(DISTINCT c.CaseID),
  100.0 * COUNT(DISTINCT CASE WHEN ou.OutcomeName = 'Hospitalization' THEN c.CaseID ELSE NULL END) / COUNT(DISTINCT c.CaseID),
  100.0 * COUNT(DISTINCT CASE WHEN ou.OutcomeName = 'Life Threatening' THEN c.CaseID ELSE NULL END) / COUNT(DISTINCT c.CaseID)
FROM Consumes con
   INNER JOIN [Case] c ON con.FKCaseID = c.CaseID
   INNER JOIN Manufacturer m ON m.ManufacturerID = con.FKManufacturerID
   INNER JOIN Case_Outcome oc ON oc.FKCaseID = c.CaseID
   INNER JOIN OutCome ou ON oc.FKOutcomeID = ou.OutcomeID
GROUP BY m.ManufacturerName
HAVING COUNT(DISTINCT c.CaseID) <> 0

If you need to display the manufactures with zero Events for all categories, remove Having and add the statement to handle 'Division by zero'. Also add the aliases for all output columns.

Comments