Report showing Tickets that are open and that are older than 6 days grouped by owner and then date-range (4306000)

CODE WRITEN BY KACE SUPPORT FOR DEMONSTRATION PURPOSES ONLY.  CLIENT CONFIRMS THE NEED FOR TESTING AND ACCEPTS ALL RESPONSABILITY FOR USE AND MISUSE OF CODE. KACE SHALL NOT BE HELD LIABLE FOR DAMAGE OR DOWN TIME RESULTING  FROM USE OF THIS OR ANY CODE PRESENTED FOR PURPOSES OF TROUBLESHOOTING OR DEMONSTRATION.

This report organizes the tickets by owner and then by date range (i.e. 7-13 days of age, 14-20 days of age and 21+ days in age) with totals for each date range. For example...

Owner - John Doe

Tickets open between 7 and 13 days
ID    Title    Created   Submitter    Status    Date of Last Comment       Last Comment
==================================================


Total Tickets open between 7 and 13 days ....
Tickets open between 14 and 20 days
ID    Title    Created   Submitter    Status    Date of Last Comment       Last Comment
==================================================

Total Tickets open between 14 and 20 days ....
Tickets open 21+ days
ID    Title    Created   Submitter    Status    Date of Last Comment       Last Comment
==================================================


Total Tickets open 21+ days ....

To create this report open a SQL blank report from the Choose Action menu, then select New (SQL).  Label this report anyting you wish.

Set the Break on Columns to:OWNER,TIME_OPENED

If you want place the checkbox next to Show Line Numbers.

Copy and past the following information in the SQL box.

select HD_TICKET.ID "Ticket",
HD_TICKET.TITLE "Title",
ifnull(O.FULL_NAME,' Unassigned') as "Owner",
Ifnull(USER.FULL_NAME,'Unassigned') as "Submitter",
HD_STATUS.NAME as "Status",
DATE(HD_TICKET.CREATED) "Created",
C.COMMENT "Comment",
C.TIMESTAMP "Comment Date",
CASE
WHEN (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(HD_TICKET.CREATED))/60/60/24 >21 THEN 'Tickets Open 21+ days'
WHEN (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(HD_TICKET.CREATED))/60/60/24 >14 THEN 'Tickets Open between 14 and 20 days'
WHEN (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(HD_TICKET.CREATED))/60/60/24 >7 THEN 'Tickets Open between 7 and 13 days'
END TIME_OPENED
from HD_TICKET JOIN
HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID
JOIN (Select MAX(ID) MAXID FROM HD_TICKET_CHANGE GROUP BY HD_TICKET_ID) MAXC ON C.ID=MAXID
LEFT JOIN USER ON SUBMITTER_ID=USER.ID
LEFT JOIN USER O ON OWNER_ID=O.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
WHERE
UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(HD_TICKET.CREATED)>(7*24*60*60)
and HD_STATUS.STATE<>'Closed'
ORDER BY O.USER_NAME ASC,(UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(HD_TICKET.CREATED)) ASC