Maximo Open Forum

HI all, In SQL Server, I'm attempting to create a query that will select all workorders based on the reportdate generated the previous week, no matter what day of the current week I'm in.  I did this for a monthly view, but struggling with the weekly range.  I have looked at the datepart function, but that only looks at current year, so going back to last weeks workorders wont work.  Any suggestions would be helpful.  Thanks
#Analytics

------------------------------
Brian Swanson
Herman Miller
------------------------------

HI Aaron,
I think I got it.  I may need to deal with the timestamp, but seems to be working..  Thanks..

select wonum, reportdate from workorder where reportdate > dateadd(day, -datepart(weekday, getdate()), getdate()) - 6 and reportdate < dateadd(day, -datepart(weekday, getdate()), getdate())
order by reportdate

------------------------------
Brian Swanson
Herman Miller
------------------------------

Original Message:
Sent: 01-07-2021 11:51
From: Aaron Paroulek
Subject: Select workorders generated from previous week

Hi Brian - I think I found a similar request to the challenge you have described.  I would recommend you use the dateadd function combined with the datepart function, to define the "Starting Date" of the prior week.  You can then add 7 days to the "Starting Date", to give you your "Ending Date".

You would then make sure that the Work Order's Reported Date is between your "Starting Date" and your "Ending Date".  (i.e. reportdate between "Starting Date" and "Ending Date")

As an example, here's a snippet that I think should explain how they can be tied together:  dateadd(day, -datepart(weekday, getdate()), getdate()) as datePeriodStart

If this doesn't work, let me know and I'll get connected to a SQL Server Database and confirm the above details.

Thanks!

Aaron Paroulek

Maven Asset Management