Maximo Open Forum

 View Only
  • 1.  Select workorders generated from previous week

    Posted 01-07-2021 11:37
    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
    ------------------------------


  • 2.  RE: Select workorders generated from previous week

    Posted 01-07-2021 11:57
    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





  • 3.  RE: Select workorders generated from previous week

    Posted 01-07-2021 12:39
    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
    ------------------------------