SLA Reports
I have been trying to make a report/s that show SLAs i.e
Overdue tickets by Owner
Overdue tickets by Category
Overdue tickets by Priority
seems i need a SQL report to achieve this for i cant get around it using the report wizard?
Overdue tickets by Owner
Overdue tickets by Category
Overdue tickets by Priority
seems i need a SQL report to achieve this for i cant get around it using the report wizard?
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
dchristian
13 years ago
bmatore,
I think this will do it.
Overdue by Owner:
Overdue by Category:
Overdue by Priority:
I think this will do it.
Overdue by Owner:
SELECT Coalesce(U.FULL_NAME, 'Unassigned') AS OWNER,
COUNT(*) AS 'Over Due Tickets'
FROM HD_STATUS S,
HD_TICKET T
LEFT JOIN USER U
ON T.OWNER_ID = U.ID
WHERE T.HD_STATUS_ID = S.ID
AND S.STATE <> 'CLOSED'
AND T.DUE_DATE <= SYSDATE()
GROUP BY OWNER
ORDER BY 'Over Due Tickets' DESCOverdue by Category:
SELECT C.NAME AS CATEGORY,
COUNT(*) AS 'Over Due Tickets'
FROM HD_TICKET T,
HD_STATUS S,
HD_CATEGORY C
WHERE T.HD_STATUS_ID = S.ID
AND C.ID = T.HD_CATEGORY_ID
AND S.STATE <> 'CLOSED'
AND T.DUE_DATE <= SYSDATE()
GROUP BY CATEGORY
ORDER BY 'Over Due Tickets' DESC
Overdue by Priority:
SELECT P.NAME AS PRIORITY,
COUNT(*) AS 'Over Due Tickets'
FROM HD_TICKET T,
HD_STATUS S,
HD_PRIORITY P
WHERE T.HD_STATUS_ID = S.ID
AND P.ID = T.HD_PRIORITY_ID
AND S.STATE <> 'CLOSED'
AND T.DUE_DATE <= SYSDATE()
GROUP BY PRIORITY
ORDER BY 'Over Due Tickets' DESC
Comments:
-
Hello dchristian, this is an old post... but I'll ad my 2 cents worth and maybe find a solution to my problem it this thread picks up again :-) ...
I'm not sure this report will be accurate since DUE_DATE is in the form yyyy-mm-dd and SYSDATE is in the form yyy-mm-dd hh:mm:ss; so, you can count anything that is less than SYSDATE, but I don't think you can ever do anything that is less than AND equal to SYSDATE... or at least that is why I think the report I've been attempting to build doesn't add up... the sum of missed due dates plus made due dates should equal the total owned tickets, but it doesn't...
SELECT LOCATION, FULL_NAME,
COUNT(HD_TICKET.ID) AS 'Total Tickets',
SUM(IF(HD_STATUS.NAME = 'closed',1,0)) AS 'Closed',
SUM(IF(HD_STATUS.NAME != 'closed',1,0)) AS 'Open',
SUM(IF(HD_STATUS.STATE = 'stalled',1,0)) AS 'Stalled',
SUM(IF(TIME_CLOSED>DUE_DATE,1,0)) AS 'Missed Due Date',
SUM(IF(TIME_CLOSED<=DUE_DATE,1,0)) AS 'Made Due Date',
ROUND (SUM(IF(TIME_CLOSED<DUE_DATE,1,0))/SUM(IF(HD_STATUS.NAME = 'closed',1,0))*100) AS '% Closed By Due Date'
FROM HD_TICKET
LEFT JOIN USER on USER.ID = HD_TICKET.OWNER_ID
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
GROUP BY LOCATION, FULL_NAME
ORDER BY LOCATION, FULL_NAME - kpm8 11 years ago
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
