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
There are no answers at this time