/build/static/layout/Breadcrumb_cap_w.png

Is there a way to exclude the created time from a report

I am looking to create a report that shows how many tickets were created on a daily basis for the month. I set it up with the Wizard (I do not know SQL) and set it to break headers on Created thinking it would give me x # of rows per day, but it give a row for each ticket because it includes the time. 

Any help on this would be great. 

Thank in Advance. 

0 Comments   [ + ] Show comments

Answers (1)

Posted by: Druis 3 years ago
Third Degree Green Belt
0
Instead of having HD_TICKET.CREATED in your report query, replace it with DATE_FORMAT(HD_TICKET.CREATED, '%d-%m-%Y') AS C_DATE. This will return the date only without the time.

Comments:
  • Thanks for the response. I am creating it with the Wizard, when I go to edit SQL this is what I have

    SELECT HD_TICKET.CREATED, O.FULL_NAME AS OWNER_NAME, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.TITLE, HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.RESOLUTION FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_TICKET.CREATED < '2017-07-01 00:00:00') AND (HD_TICKET.CREATED > '2017-05-31 00:00:00')) ORDER BY CREATED, OWNER_NAME

    I have tried inserting or replacing what you suggested in multiple locations but it gives me an error everytime. Any suggestions would be appreciated. Like I said I am not good at SQL. Thanks. - DJSlater 3 years ago
    • Sorry, I had not fully read through your question. Try this:-

      SELECT COUNT(DATE_FORMAT(HD_TICKET.CREATED, '%d-%m-%Y')) AS T_COUNT,
      DATE_FORMAT(HD_TICKET.CREATED, '%d-%m-%Y') AS C_DATE,
      O.FULL_NAME AS OWNER_NAME,
      S.FULL_NAME AS SUBMITTER_NAME,
      HD_TICKET.TITLE,
      HD_CATEGORY.NAME AS CATEGORY,
      HD_TICKET.RESOLUTION FROM HD_TICKET
      LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
      LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
      JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
      WHERE (HD_TICKET.HD_QUEUE_ID = 1)
      AND ((HD_TICKET.CREATED < '2017-07-01 00:00:00') AND (HD_TICKET.CREATED > '2017-05-31 00:00:00'))
      GROUP BY C_DATE
      ORDER BY HD_TICKET.CREATED, OWNER_NAME - Druis 3 years ago
      • Thanks, it takes that SQL but when I try to run the report I get Error Running Report

        The query does not contain specified break field.

        Really appreciate the help with this. - DJSlater 3 years ago
    • I'm not sure where the error is. Try to create a 'New (SQL)' report from the action menu, rather than editing the one from the Wizard - Druis 3 years ago
    • Also I would highly recommend downloading a Query tool like MySQL workbench or Heidi SQL to test your queries. - Druis 3 years ago

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