/build/static/layout/Breadcrumb_cap_w.png

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,
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:
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)

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 Yellow Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ