I am trying to create a report that would show the closed tickets for all users in a label for the previous 7 days. I can almost get the entire report to work except the part to show only the prevous 7 days. In sql I can use a between sysdate-7 and sysdate. Is there a mysql way of doing this?

This is the report code I am working with.


select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_STATUS.NAME as TICKET_STATUS,
HD_CATEGORY.NAME as CATEGORY,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
left join USER_LABEL_JT on HD_TICKET.OWNER_ID = USER_LABEL_JT.USER_ID
where HD_STATUS.STATE = 'closed'
and USER_LABEL_JT.LABEL_ID = 193
and HD_TICKET.TIME_CLOSED between sysdate() and sysdate() - 30
order by OWNER_NAME, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
I did not think about using the query builder to find this answer.
I think (date(HD_TICKET.TIME_CLOSED) > Date_SUB(NOW(), INTERVAL 7 Day) and (date(HD_TICKET.TIME_CLOSED)<=NOW()))
will give me the previous 7 days.
Answered 01/05/2012 by: young020
Black Belt

Please log in to comment
0
in MySQL NOW() is the current timestamp (e.g. 2012-01-05 10:20:00) on the kbox and CURDATE() (e.g. 2012-01-05 00:00:00) is the current datestamp without time (or midnight).

Since neither TIME_CLOSED nor NOW() will ever be in the future you could use:

and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)

or

and DATE(HD_TICKET.TIME_CLOSED) > DATE_SUB(CURDATE(), INTERVAL 7 DAY)

or some other combination depending upon your boundary needs

Lastly, do you have users in multiple labels? If so then you may want to add a group by on the USER_LABEL_JT.USER_ID
Answered 01/05/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Thank you for the reply this makes it more simplified. Out of the users I am working with they are currently only in 1 label. I will keep in mind to group them if I expand them into multiple labels.
Answered 01/05/2012 by: young020
Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity