question

simat75 avatar image
simat75 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
calculated-column
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
SQLStu avatar image
SQLStu answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.