asked
Calculate percentage of Closed result
I have the below query, where I am getting Raised and Closed columns. I want third column as Percentage of Closed. How can I get this? SELECT b.CTR_SUPERVISORS as "Department", count(*) as "Received", SUM(case when (a.TA_STATUS in ('COMPLETE','HISTORY')) THEN 1 ELSE 0 END) AS "Closed" FROM F_TASKS a inner join F_CONTRACT b on a.TA_FKEY_CTR_SEQ = b.CTR_SEQ where a.TA_FKEY_BG_SEQ in (7,8,9) and a.TA_TASK_DESC in ('BREAKDOWN', 'SCHEDULEDTASK') AND (a.TA_HIST_STATUS IS NULL OR a.TA_HIST_STATUS <> 'CANCELLED') and b.CTR_SUPERVISORS <> '' group by b.CTR_SUPERVISORS
It seems to me that you might benefit from a [common table expression][1]: - ;WITH CTE AS ( SELECT b.CTR_SUPERVISORS AS "Department", COUNT(*) AS "Received", SUM(CASE WHEN(a.TA_STATUS IN ('COMPLETE', 'HISTORY') ) THEN 1 ELSE 0 END) AS "Closed" FROM F_TASKS AS a INNER JOIN F_CONTRACT AS b ON a.TA_FKEY_CTR_SEQ = b.CTR_SEQ WHERE a.TA_FKEY_BG_SEQ IN (7, 8, 9) AND a.TA_TASK_DESC IN ('BREAKDOWN', 'SCHEDULEDTASK') AND (a.TA_HIST_STATUS IS NULL OR a.TA_HIST_STATUS <> 'CANCELLED') AND b.CTR_SUPERVISORS <> '' GROUP BY b.CTR_SUPERVISORS) SELECT CTE.Department, CTE.Received, CTE.Closed, (CTE.Closed * 100 / CTE.Received) AS [Percentage of Closed] FROM CTE; [1]:
https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql
22 People are following this question.
