Tickets Closed By Label

Question

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

Answer

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.

Answer

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

Answer

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.

Tickets Closed By Label

Answers (3)

Posted by:

View more:

Related Questions

Related Links

Related Posts

ITNinja

Tickets Closed By Label

Answers (3)

Posted by:

Don't be a Stranger!

View more:

Related Questions

Related Links

Related Posts

Share