/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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