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
;
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]