I created a report for our Change Management queue by using the report wizard. I used Kace's built-in advanced date filters to show tickets that have a start date (custom_field_value1) from last week, this week, and next week. I then edited the SQL to add a variable that represents weekofyear for custom_field_value1, so I can order it by the weekofyear. 

I have four tickets in the queue. Two are for last week, one is for this week, and one is for next week. When I ran the report yesterday, it didn't include the ticket for next week in the results. However, when I run it today, it does include that ticket. It should have been on the report yesterday but it was not, so I know something with the date caculations is wrong. I didn't change any of the SQL for the date calculations - those all came from the report wizard. Can anyone please help? 

SELECT HD_STATUS.NAME AS STATUS,

HD_TICKET.TITLE,

HD_CATEGORY.NAME AS CATEGORY,

HD_TICKET.CUSTOM_FIELD_VALUE0 AS ENVIRONMENT,

HD_PRIORITY.NAME AS PRIORITY,

HD_TICKET.CUSTOM_FIELD_VALUE4 AS IMPACT,

HD_TICKET.CUSTOM_FIELD_VALUE1 AS 'START DATE/TIME',

HD_TICKET.CUSTOM_FIELD_VALUE2 AS 'END DATE/TIME',

HD_TICKET.CUSTOM_FIELD_VALUE5 AS 'BACK OUT PLAN',

HD_TICKET.CUSTOM_FIELD_VALUE6 AS 'JIRA TICKET URL',

weekofyear(HD_TICKET.CUSTOM_FIELD_VALUE1) as ChangeWeek,

S.FULL_NAME AS SUBMITTER_NAME FROM HD_TICKET  

JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) 

JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) 

JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) 

LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) 

WHERE (HD_TICKET.HD_QUEUE_ID = 8) AND ((date(HD_TICKET.CUSTOM_FIELD_VALUE1) <= date_add(curdate(),

interval dayofweek(curdate()) - 1 + (7*1)  day)  and date(HD_TICKET.CUSTOM_FIELD_VALUE1) >= date_add(curdate(),

interval 8-dayofweek(curdate())  day) ) OR (date(HD_TICKET.CUSTOM_FIELD_VALUE1) >= date_sub(curdate(),

interval dayofweek(curdate()) - 1 day)  and weekofyear(HD_TICKET.CUSTOM_FIELD_VALUE1) = weekofyear(curdate()) ) OR (date(HD_TICKET.CUSTOM_FIELD_VALUE1) >= date_sub(curdate(),

interval dayofweek(curdate()) - 1 + (7*1)  day)  and date(HD_TICKET.CUSTOM_FIELD_VALUE1) < date_add(curdate(),

interval dayofweek(curdate()) - 1 day) ))  

ORDER BY ChangeWeek
3 Comments   [ + ] Show Comments

Comments

  • You might run into problems with using dayofweek and weekofyear because those will match other years. I'm not sure if that's causing the problem here or not. The select statements for getting dates equal to last week, this week and next week are pretty complicated.
  • Here's another way to get tickets from last week, this week and next week:
    weekofyear(HD_TICKET.CUSTOM_FIELD_VALUE1) between weekofyear(now() - INTERVAL 1 WEEK) and weekofyear(NOW() + INTERVAL 1 WEEK) and year(HD_TICKET.CUSTOM_FIELD_VALUE1) = year(now());
  • I like the way you think!!! That looks like it will work. Thanks a ton. As far as matching other years... right now everything is for this year so it wasn't causing problems yet. I will need to think of another way to handle that. Maybe we'll delete changes that are over a year old.
    • That's why I added a statement to match the year of the CUSTOM_FIELD_VALUE1 to this year.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity

Share