January 17, 2012 at 10:48 pm
WITH CTE AS
(
SELECT MNTH, YR,
Sev1Count,Total_Count,
CAST(CAST(ROUND(CAST(Sev1Count AS DECIMAL(12,2))/ CAST(Total_Count AS DECIMAL(12,2)) * 100, 0) AS INT) AS VARCHAR(10)) + ' %' Performance,
Hr1,
Hr2,
Hr3,
ROUND((CAST(Hr1 AS DECIMAL(12,2))/ CAST(Sev1Count AS DECIMAL(12,2)) * 100, 0) Hr1Percentage,
ROUND((CAST(Hr2 AS DECIMAL(12,2))/ CAST(Sev1Count AS DECIMAL(12,2)) * 100, 0) Hr2Percentage,
ROUND((CAST(Hr3 AS DECIMAL(12,2))/ CAST(Sev1Count AS DECIMAL(12,2)) * 100, 0) Hr3Percentage
FROM
(
SELECT DATENAME(Month,Close_Time) MNTH, YEAR(Close_Time) YR,
COUNT(Impact) Total_Count,
COUNT(CASE WHEN Impact = 'Sev 1' THEN 1 ELSE NULL END) Sev1Count,
COUNT(CASE WHEN DATEDIFF(HH, Open_Time, Close_Time) <= 2 THEN 1 ELSE NULL END) Hr1,
COUNT(CASE WHEN DATEDIFF(HH, Open_Time, Close_Time) > 2 AND DATEDIFF(HH, Open_Time, Close_Time) <= 4 THEN 1 ELSE NULL END) Hr2,
COUNT(CASE WHEN DATEDIFF(HH, Open_Time, Close_Time) > 4 THEN 1 ELSE NULL END) Hr3
FROM @T
GROUP BY DATENAME(Month,Close_Time), YEAR(Close_Time)
) TBL
)
SELECT MNTH, YR,
Sev1Count,Total_Count,
Performance,
Hr1,
Hr2,
Hr3,
CASE
WHEN Hr1Percentage >= 90 AND Hr2Percentage = 100 THEN 'Green'
WHEN Hr1Percentage < 90 OR Hr2Percentage < 100 THEN 'Red'
WHEN Hr1Percentage < 90 AND Hr2Percentage < 100 THEN 'Purple'
END Color
FROM CTE
**********************************************
from above code i am getting following errors.
Getting syntax errors
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ','.
ROUND((CAST(Hr1 AS DECIMAL(12,2))/ CAST(Sev1Count AS DECIMAL(12,2)) * 100, 0) Hr1Percentage,
ROUND((CAST(Hr2 AS DECIMAL(12,2))/ CAST(Sev1Count AS DECIMAL(12,2)) * 100, 0) Hr2Percentage,
ROUND((CAST(Hr3 AS DECIMAL(12,2))/ CAST(Sev1Count AS DECIMAL(12,2)) * 100, 0) Hr3Percentage
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'TBL'.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply