Count tickets open/not due and closed/overdue
I need to create a report that counts tickets created within a time period (last 31 and last 7) which match these criteria
Closed < Due Date or Open and Due Date > Today
and
Closed > Due Date or Open and Due Date < Today
I used with Altiris to dump the tickets created in the date range and use excel date calcs to work out the rest, but the format KACE reporting dumps ticket data in causes havoc with Excel date calculations - it sees anything before month 10 as a string and anything after as a serial number. Aaagh!
Any help much appreciated :)
Closed < Due Date or Open and Due Date > Today
and
Closed > Due Date or Open and Due Date < Today
I used with Altiris to dump the tickets created in the date range and use excel date calcs to work out the rest, but the format KACE reporting dumps ticket data in causes havoc with Excel date calculations - it sees anything before month 10 as a string and anything after as a serial number. Aaagh!
Any help much appreciated :)
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
GillySpy
12 years ago
dumps ticket data in causes havoc with Excel date calculations
If you think it is the kbox you should open a ticket, but sometimes excel needs to be told that a column is holding date types instead of "general" types
Here's the basics for the count:
select COUNT(*) from HD_TICKET WHERE CREATED > DATE_SUB(CURDATE(), INTERVAL 31 DAY)Adding "Open and Due Date > Today":
select COUNT(*) from HD_TICKET
JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
WHERE CREATED > DATE_SUB(CURDATE(), INTERVAL 31 DAY)
and S.STATE='Opened'
and DUE_DATE> CURDATE()Other combinations should follow from those
Comments:
-
Can this SQl be edited to show ticket count for a specific date range? what would that look like? - bryscott91 5 years ago
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
