February 1, 2016 at 6:12 am
Hi,
I need some help with pivot table and calculations.
CREATE TABLE #Derived
(
ID INT IDENTITY(1,1),
Domain VARCHAR(10),
CaseName VARCHAR(100),
Type VARCHAR(25),
Stream CHAR(3),
Status VARCHAR(20)
)
INSERT INTO #Derived
(
Domain,CaseName,Type,Stream,Status
)
SELECT 'ABC','ABC Case 1','Focussed','ATR','N/A' UNION ALL
SELECT 'ABC','ABC Case 1','Focussed','ATR','N/A' UNION ALL
SELECT 'ABC','ABC Case 1','Focussed','ATR','Passed' UNION ALL
SELECT 'ABC','ABC Case 1','Focussed','ATR','Passed' UNION ALL
SELECT 'ABC','ABC Case 1','Focussed','ATR','Passed' UNION ALL
SELECT 'XYZ','XYZ Case 1','Focussed','ATR','Failed' UNION ALL
SELECT 'XYZ','XYZ Case 1','Focussed','ATR','Failed' UNION ALL
SELECT 'XYZ','XYZ Case 1','Focussed','ATR','Passed' UNION ALL
SELECT 'MNO','MNO Case 1','Focussed','DAM','Failed' UNION ALL
SELECT 'MNO','MNO Case 1','Focussed','DAM','N/A' UNION ALL
SELECT 'MNO','MNO Case 1','Focussed','DAM','n/A' UNION ALL
SELECT 'MNO','MNO Case 1','Focussed','DAM','Passed' UNION ALL
SELECT 'MNO','MNO Case 1','Focussed','DAM','Passed' UNION ALL
SELECT 'MNO','MNO Case 1','Focussed','DAM','Not Completed' UNION ALL
SELECT 'MNO','MNO Case 1','Focussed','DAM','No Run'
SELECT * FROM #Derived
--Desired results
--1)Need the Status as columns ,
--2)count total ID for particular CaseName
--3)need new column %Complete-->(SUM(ISNULL([Passed],0)) + SUM(ISNULL([N/A],0)) )/IDCount AS [% Complete]
SELECT 'ABC' AS Domain,'ABC Case 1' AS CaseName,'Focussed' AS Type,'ATR' AS Stream,3 AS Passed,2 AS 'N/A',0 AS 'Failed',0 AS 'Not Completed',0 AS 'No Run',5 AS IDCount,100 AS '% Complete' UNION ALL
SELECT 'XYZ' AS Domain,'XYZ Case 1' AS CaseName,'Focussed' AS Type,'ATR' AS Stream,1 AS Passed,2 AS 'Failed',0 AS 'N/A',0 AS 'Not Completed',0 AS 'No Run',3 AS IDCount,33.3 AS '% Complete' UNION ALL
SELECT 'MNO' AS Domain,'MNO Case 1' AS CaseName,'Focussed' AS Type,'DAM' AS Stream,2 AS Passed,1 AS 'Failed',2 AS 'N/A',1 AS 'Not Completed',1 AS 'No Run',7 AS IDCount,57.14 AS '% Complete'
DROP TABLE #Derived
Thanks,
PSB
February 1, 2016 at 7:43 am
Quick suggestion
😎
SELECT
D.Domain
,D.CaseName
,D.Type
,D.Stream
,SUM(CASE WHEN D.[Status] = 'Passed' THEN 1 ELSE 0 END) AS Passed
,SUM(CASE WHEN D.[Status] = 'N/A' THEN 1 ELSE 0 END) AS [N/A]
,SUM(CASE WHEN D.[Status] = 'Failed' THEN 1 ELSE 0 END) AS Failed
,SUM(CASE WHEN D.[Status] = 'Not Completed' THEN 1 ELSE 0 END) AS [Not Completed]
,SUM(CASE WHEN D.[Status] = 'No Run' THEN 1 ELSE 0 END) AS [No Run]
,COUNT(*) AS IDCount
,100 * (CONVERT(NUMERIC(5,2), (SUM(CASE WHEN D.[Status] = 'Passed' THEN 1 ELSE 0 END)
+ SUM(CASE WHEN D.[Status] = 'N/A' THEN 1 ELSE 0 END)
) / (COUNT(*) + 0.0),0)) AS [% Complete]
FROM #Derived D
GROUP BY D.Domain
,D.CaseName
,D.Type
,D.Stream;
Results
Domain CaseName Type Stream Passed N/A Failed Not Completed No Run IDCount % Complete
---------- ----------- ---------- ------ ----------- ----------- ----------- ------------- ----------- ----------- -----------
ABC ABC Case 1 Focussed ATR 3 2 0 0 0 5 100.00
MNO MNO Case 1 Focussed DAM 2 2 1 1 1 7 57.00
XYZ XYZ Case 1 Focussed ATR 1 0 2 0 0 3 33.00
February 1, 2016 at 7:48 am
Few ways to do it, I've always preferred the cross-tabs
SELECT [pvt].[Domain],
[pvt].[CaseName],
[pvt].[Stream],
[pvt].[Passed],
[pvt].[N/A],
[pvt].[Failed],
[pvt].[Not Completed],
[pvt].[No Run],
[pvt].[IDCount],
CAST(( ( [pvt].[Passed] + [pvt].[N/A] * 1.0 ) / ( [pvt].[IDCount] ) )
* 100 AS NUMERIC(19, 2)) AS [% Complete]
FROM ( SELECT [Domain],
[CaseName],
[Stream],
SUM(CASE WHEN Status = 'Passed' THEN 1
ELSE 0
END) AS [Passed],
SUM(CASE WHEN Status = 'N/A' THEN 1
ELSE 0
END) AS [N/A],
SUM(CASE WHEN Status = 'Failed' THEN 1
ELSE 0
END) AS [Failed],
SUM(CASE WHEN Status = 'Not Completed' THEN 1
ELSE 0
END) AS [Not Completed],
SUM(CASE WHEN Status = 'No Run' THEN 1
ELSE 0
END) AS [No Run],
SUM(1) AS [IDCount]
FROM #Derived
GROUP BY Domain,
CaseName,
Stream
) [pvt];
Results in: -
Domain CaseName Stream Passed N/A Failed Not Completed No Run IDCount % Complete
---------- ---------------------------------------------------------------------------------------------------- ------ ----------- ----------- ----------- ------------- ----------- ----------- ---------------------------------------
ABC ABC Case 1 ATR 3 2 0 0 0 5 100.00
MNO MNO Case 1 DAM 2 1 1 1 1 7 42.86
XYZ XYZ Case 1 ATR 1 0 2 0 0 3 33.33
Please note, I make MNO completion rate 42.86 (3 out of 7) and XYZ 33.33 (1 out of 3) rather than the 57.14 and 33.30 that you made it.
February 1, 2016 at 8:44 am
Thank you. Worked perfectly.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply