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]