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

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

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
Answered 11/04/2010 by: dchristian
Red Belt

  • 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
Please log in to comment
Answer this question or Comment on this question for clarity