Getting Fancy with Kace Reporting - using "Case"
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)
Please log in to answer
Posted by: reneed33 5 years ago