KACE Product Support Question
Getting Fancy with Kace Reporting - using "Case"
09/25/2015 1493 views
I am trying to produce a particular custom SQL report for my K1000 Service Desk queue, I am hoping some of you SQL geniuses might be able to help me with a particular vexing thing I have not been able to figure out.
So basically, I want to essentially create a new column in my report that will summarize ticket creation dates using "CASE" in mysql.
For instance, I want to do the following:
- If the ticket creation date was in the last 20 days, then mark that as "RECENT"
- Else, it is "OLDER"
- The column that will hold these results for each row is called "Schedule"
My syntax is correct- I do not get errors, however everything shows under the "else" category even it if really shouldn't be.
It has to be something about the way the date is formatted in the created field? Help, please? TIA! :-)
HERE IS A SNIPPET OF THE QUERY:
HD_STATUS.NAME AS STATUS_NAME,
O.FULL_NAME AS OWNER_NAME,
O.CUSTOM_3 AS SECTION,
WHEN HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 20 DAY) THEN 'RECENT'
END AS 'SCHEDULE'
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
JOIN HD_IMPACT ON (HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID)
Answer Chosen by the Author
Please log in to answer
answering my own question... Here is what works! :)
O.CUSTOM_3 AS SECTION,HD_TICKET.CREATED,CASE WHEN DATE(HD_TICKET.CREATED) > DATE_SUB(NOW(), INTERVAL 20 DAY) THEN 'RECENT' ELSE 'OLDER'END AS 'SCHEDULE'FROM HD_TICKET
Answered 09/25/2015 by: reneed33