/build/static/layout/Breadcrumb_cap_w.png

How do I fix these date calculations that are only slightly off?

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
  • 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. - chucksteel 10 years ago
  • 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()); - chucksteel 10 years ago
  • 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. - ashlea 10 years ago
    • That's why I added a statement to match the year of the CUSTOM_FIELD_VALUE1 to this year. - chucksteel 10 years ago

Answers (0)

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ