Conditional statement counts with CTE

Question

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

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.

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.

But my percentage result set looks like the following.

I don't understand the question. You don't really have a `where` condition in your query (apart from the correlation clause). — Gordon Linoff, Aug 15 '16 at 01:08

score 1 · Accepted Answer · edited May 23 '17 at 12:00

1

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.

UPDATE:

If the requirement is to use only output of CTE, then the data can be transformed in this way:

declare @sql NVARCHAR(MAX) = '
declare @temp table (case_id varchar(100), manufacture varchar(1000), outcome varchar(max))

insert into @temp
'

select @sql = @sql + 'select ''' + t.case_id + ''', ''' + t.Manufacture + ''', s.Data from dbo.Split(''' + t.outcome + ''', '','') s union all' + char(13) + char(10)
from @t t

set @sql = LEFT(@sql, len(@sql) - 12) + char(13) + char(10) + char(13) + char(10)

set @sql = @sql + 'select * from @temp'

--print @sql
exec (@sql)

You need to implement Split function (many implementations available). Change the column name as per your design. The output of EXEC can be inserted into temp table

INSERT #TEMP (...)
EXEC (@SQL)

Alternatively you can loop through each value of CTE output and use Split function to get the table with values, and populate temp table.

Finally use the method mentioned earlier in the comments, or use PIVOT function with unknown number of columns as described here: Efficiently convert rows to columns in sql server

edited May 23 '17 at 12:00

Community

1
1

answered Aug 15 '16 at 01:09

Anton

2,846
1
10
15

This works fine when all the outcomes are the same for a case. When there are multiples outcomes for the same case, this doesn't give the correct output. I have edited the question with the output I get. – AnOldSoul Aug 15 '16 at 15:09
I didn't have a chance to check my script as you did not provide tables' definititon. The problem is minor - it's because of rounding - just move "* 100.00" in front of "COUNT(...". "select 3/9 * 100.0" is not the same as "100.0 * 3 / 9" – Anton Aug 15 '16 at 23:18
hey without hardcoding the cases, is there a way I can extract them from the outcome column's comma separated values and calculate percentage from that? – AnOldSoul Aug 15 '16 at 23:38
Yes, it's possible by multiple ways. The easiest for me is to extract the outcome values to temp table #t, then declare NVARCHAR(MAX) "@sql" variable, set it to "SELECT m.ManufacturerName, COUNT(DISTINCT c.CaseID),", build that variable like select "@sql" = "@sql" + '100.00 * COUNT... WHEN coutcome = #t.value THEN ...,' from #t. Then remove the last comma, then add the rest of code 'FROM ....', then execute it as dynamic sql. You can insert the results to another temp table. – Anton Aug 15 '16 at 23:50
Can you provide an example using xml or cte? :( – AnOldSoul Aug 15 '16 at 23:52
If you need outcome in rows, not columns, it's even easier. Populate temp table first, but with Outcome ID, not name. The select from that temp table, joining all other tables. You can find the total of cases per manufacture in a separate query if the query is too complicated. – Anton Aug 15 '16 at 23:53
I don't really understand why do you need to use CTE? Is it requirement? – Anton Aug 15 '16 at 23:54
yeah the requirement is to use either CTE or XML – AnOldSoul Aug 15 '16 at 23:55
So you cannot use the tables "Consumes ", "Manufacture", etc. directly, right? – Anton Aug 15 '16 at 23:57
No its just that XML or CTE should be used for string split. Apart from that no other restrictions – AnOldSoul Aug 16 '16 at 00:00
Are you able to create Split function in your DB that inputs varchar(max) string and returns the table with values? (there are many available implementations) – Anton Aug 16 '16 at 00:05
that's good. first, implement the split function. See these links http://sqlperformance.com/2012/07/t-sql-queries/split-strings and http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql and choose the best for you – Anton Aug 16 '16 at 00:12
Updated script. Are you able to construct dynamic SQL mentioned in comment #4? – Anton Aug 16 '16 at 01:05

Conditional statement counts with CTE

1 Answers1