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)
Please log in to answer
Posted by:
Druis
7 years ago
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 7 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 7 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 7 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 7 years ago
-
Also I would highly recommend downloading a Query tool like MySQL workbench or Heidi SQL to test your queries. - Druis 7 years ago
