One of the tenets I teach about reporting in Maximo is to look for the exceptions in the data, not the data itself. Out of the box, Maximo has quite a few key reports included, one of them is the WO List report (wotrack.rptdesign). That report lists every work order contained in the current search criteria. And if that search criteria is empty, you'll get every open work order.

For the company I work for, we've looked at our work orders based on the work week and reporting period. For us, our work week is from Saturday through Friday. ¹ So when we report on upcoming work, it was natural for us to look at work orders only from the next Saturday to the Friday after that. So if today is 2018-10-02, then we'd want to see a date range of Sat (2018-10-06) through Fri (2018-10-12).

When testing date queries, most beginners use variation of getdate(). So if we test a SQL query like what is the first day of next month

select dateadd(month, datediff(month, 1, getdate() ) +0,0)

That kind of query can be tested on any day of the month - it works or it doesn't.

But then there are times when we want to have a query find a specific day of the week, like this coming Saturday. That requires some more complex query statements and we need to check to see if the calendar date rolls over or not. For example, if we wanted to find the the date of this coming Saturday.

select dateadd(wk, datediff(wk, -8, getdate() ), -2)

While the statement works, the question is does it work for each day of the week?

We can temporarily set a date value and then be able to change the date to see how the query rolls through the rest of the week.

declare @calendardate as datetime
set @calenderdate = '10/6/2018'
select dateadd(wk, datediff(wk, -8, @calenderdate), -2)
select dateadd(wk, datediff(wk, -15, @calenderdate), -2)

Now I have a way to test date/time queries to see how they change through the week. But this method means I need to manually iterate the dates over the next 7 days. I'd prefer to have a method to roll through the next 7 days and output the results. In SQL you can iterate over a data set. So to test the date modification dateadd(wk, datediff(wk, -8, getdate() ), -2), we can use the following query statement.

declare @calendardate as datetime
set @calendardate = {ts ' 2018-10-01 12:00:00.000'}

while (select @calendardate) < getdate() + 7  
begin
    set @calendardate = dateadd(d, 1, @calendardate)
    select datename(dw, @calendardate), @calendardate, 
    dateadd(wk, datediff(wk, -8, @calendardate), -2)
    if (select @calendardate) >   getdate() + 7
        break  
    else  
        continue  
end

Now I can change 1 line out of this statement in the future to test other rolling date queries.