Miscellaneous

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