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,
HD_TICKET.CREATED,
CASE HD_TICKET.CREATED
WHEN  HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 20 DAY)    THEN 'RECENT'
  ELSE 'OLDER'
END AS 'SCHEDULE'

FROM HD_TICKET  
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) 
JOIN HD_IMPACT ON (HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID) 

RESULTS
Ds7b4r.jpeg

Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

Comments

  • For this example, you could also use an IF statement. In true/false situations like this, it is usually simpler and easier to read.
    http://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html#function_if
  • Thank you grayematter! The actual query I was trying to achieve had 3 "ifs" - but I only showed two for simplicity sake (which made it seem like a simple true/false or yes/no)
Please log in to comment

Answer Chosen by the Author

0
answering my own question... Here is what works! :)

qetQYw.jpeg

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  


VICTORY!

Answered 09/25/2015 by: reneed33
Senior White Belt

Please log in to comment

Answers

Answer this question or Comment on this question for clarity