Reporting using custom fields
We have a report that is designed to bring back data on closed tickets by department (our custom field) for the past quarter and the select statement we have seems to pull all data rather than the dates we are trying to pull from. any help would be appreciated.
SELECT U.CUSTOM_1 AS DEPARTMENT,
COUNT(*) AS CLOSED_TICKETS
FROM HD_TICKET H,
HD_STATUS S,
USER U
WHERE H.HD_STATUS_ID = S.ID
AND H.SUBMITTER_ID = U.ID
AND S.STATE = 'CLOSED'
AND (H.TIME_CLOSED >= '1/1/11' and H.TIME_CLOSED <= '3/31/11')
GROUP BY U.CUSTOM_1
SELECT U.CUSTOM_1 AS DEPARTMENT,
COUNT(*) AS CLOSED_TICKETS
FROM HD_TICKET H,
HD_STATUS S,
USER U
WHERE H.HD_STATUS_ID = S.ID
AND H.SUBMITTER_ID = U.ID
AND S.STATE = 'CLOSED'
AND (H.TIME_CLOSED >= '1/1/11' and H.TIME_CLOSED <= '3/31/11')
GROUP BY U.CUSTOM_1
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
GillySpy
12 years ago
The time format is YYYY-MM-DD HH:MM:SS so 2011-01-01 would be a valid timestamp implying midnight on that day.
Posted by:
pedter
12 years ago
Perfect, once i made the change it pulled the correct data. thank you!
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.