/build/static/layout/Breadcrumb_cap_w.png

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?

0 Comments   [ + ] Show comments

Answers (1)

Posted by: dchristian 14 years ago
Red Belt
0
bmatore,

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' DESC

Overdue 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 12 years ago
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ