# Query help

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
(
)
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]

``````;

WITH cte
AS (
SELECT [created by],
Created,
Severity,
FROM oldinc
),
cte2
AS (
SELECT [created by],
Created,
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]``````

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

change to

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

Thank you so much,it worked