SLA Breach report account wise
Query
SELECT ad.org_name"Account",
SUM(CASE WHEN sdf.statusid='1' THEN 1 ELSE 0 END) "Count Of Open Request",
SUM(CASE WHEN sdf.statusid='3' THEN 1 ELSE 0 END) "Count Of Closed Request",
SUM(CASE(isoverdue) WHEN TRUE THEN 1 ELSE 0 END) "Count Of calls Exceeded SLA",
SUM(CASE(isoverdue) WHEN FALSE THEN 1 ELSE 1 END) "Count Of calls within SLA" ,
(SUM(CASE(isoverdue) WHEN TRUE THEN 1 ELSE 0 END)*100)/COUNT(wo.workorderid) "% Calls Exceeded SLA",
((COUNT(wo.workorderid) - SUM(CASE(isoverdue) WHEN TRUE THEN 1 ELSE 0 END))*100)/COUNT(wo.workorderid) "% Of Calls Within SLA" FROM workorder wo
LEFT JOIN workorderstates wos ON wo.workorderid = wos.workorderid
LEFT JOIN statusdefinition sdf ON wos.statusid=sdf.statusid
LEFT JOIN sitedefinition sd ON sd.siteid=wo.siteid
LEFT JOIN accountsitemapping asm ON asm.siteid=sd.siteid
LEFT JOIN accountdefinition ad ON ad.org_id=asm.accountid
WHERE wo.CREATEDTIME >= <from_lastmonth>
AND wo.CREATEDTIME <= <to_lastmonth> GROUP BY ad.org_name
How to compare date column with auto filled date templates?
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- Available Date Templates
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
Krishna Bharat
ServiceDesk Plus - MSP Support team