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 :)
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
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
Answered 10/07/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity