SQLTeam.com | Weblogs | Forums

Query help

#1

Hello,
Using the below query,I am able to get all the tickets resolved by each technician severity wise.
Now,i want to calculate the SLA for each technician and the formula would be,
Total No of tickets where SLA='Met'/Total no of tickets

;with cte
as
(
select [created by],Created,[Alert Time],Severity,datediff(MINUTE,[Alert Time],created) acktime from oldinc
)
select [created by],Created,[Alert Time],Severity, acktime,
case when severity='3 - Medium' and acktime<=15 then 'Met'
when severity='4 - Low' and acktime<=60 then 'Met'
when severity='2 - High' and acktime<=5 then 'Met'
when severity='1 - critical' and acktime<=5 then 'Met'
when acktime is null then 'UnKnown'
else

'Voilated'
end SLA
from cte order by [created by]

#2 
;

WITH cte
AS (
	SELECT [created by],
		Created,
		[Alert Time],
		Severity,
		datediff(MINUTE, [Alert Time], created) acktime
	FROM oldinc
	),
cte2
AS (
	SELECT [created by],
		Created,
		[Alert Time],
		Severity,
		acktime,
		CASE 
			WHEN severity = '3 - Medium'
				AND acktime <= 15
				THEN 'Met'
			WHEN severity = '4 - Low'
				AND acktime <= 60
				THEN 'Met'
			WHEN severity = '2 - High'
				AND acktime <= 5
				THEN 'Met'
			WHEN severity = '1 - critical'
				AND acktime <= 5
				THEN 'Met'
			WHEN acktime IS NULL
				THEN 'UnKnown'
			ELSE 'Voilated'
			END SLA
	FROM cte
	)
SELECT [created by],
	sum(CASE 
			WHEN SLA = 'Met'
				THEN 1
			ELSE 0
			END) / count(*)
FROM cte2
GROUP BY [created by]
#3

thanks Khtan.
I am not getting the correct SLA,can you please look at the attached screen shot.
I am getting 0 for the user akulam-yash

Actually,it should be 12/16=0.75 for that user

#4

change to

SELECT [created by],
	sum(CASE 
			WHEN SLA = 'Met'
				THEN 1
			ELSE 0
			END) * 1.0/ count(*)
FROM cte2
GROUP BY [created by]
#5

Thank you so much,it worked