Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only

  • 1.  Query with prior to current month

    Posted Wed February 23, 2022 07:08 AM

    I am attempting to write a query in maximo that will capture all open work orders with target complete dates prior to the current month. I can't get it to work. any suggestions on how to write the date query so that it eliminates anything with a targcompdate after the 1st of the current month?

     

    Thanks!



    ------------------------------
    Shlomo Shvartz
    ------------------------------

    #Maximo
    #AssetandFacilitiesManagement


  • 2.  RE: Query with prior to current month

    Posted Wed February 23, 2022 01:08 PM
    Edited by System Admin Wed March 22, 2023 11:46 AM
    I assume you use oracle database, here is a simple sql query
    select * from workorder where istask='0' and historyflag='0' and targcompdate < (select trunc(last_day(sysdate)-1, 'mm') from dual)

    ------------------------------
    SIVATHANU SIVAKUMAR
    Asset Management Systems Advisor
    ------------------------------

    Original Message


  • 3.  RE: Query with prior to current month

    Posted Wed February 23, 2022 01:14 PM
    The previous response should work for Oracle (and maybe DB2) databases. You didn't specify which you're using.

    This should work for SQL Server:
    select * from workorder where istask=0 and historyflag=0 and targcompdate<dateadd(dd, 1-datepart(dd, getdate()), cast(getdate() as date))


    ------------------------------
    Tim Ferrill
    Solutions Consultant
    Intelligent Technology Solutions
    tferrill@webuildits.com
    www.webuildits.com
    @tferrill/@webuildits
    ------------------------------

    Original Message


  • 4.  RE: Query with prior to current month

    Posted Thu February 24, 2022 02:28 PM
    If you are using oracle database

    select *
    from workorder 
    where istask = 0 and historyflag = 0 and targcompdate < trunc(sysdate,'month') ;

    ------------------------------
    Omar Abou Ammar
    Senior Technical Consultant
    eSolutions FZ-LLC
    Dubai
    ------------------------------

    Original Message


  • 5.  RE: Query with prior to current month

    Posted Thu February 24, 2022 02:34 PM
    Edited by System Admin Wed March 22, 2023 11:46 AM
    If you are using oracle and open work order are those where history flag is 0

    select *
    from  workorder
    where istask = 0 and historyflag =0 and targcompdate < trunc(sysdate,'month')

    If open workorders are those where not closed or canceled or completed (WAPPR,INPRG,APPR ,WSCH ... etc)

    select *
    from  workorder
    where istask = 0 and targcompdate < trunc(sysdate,'month') and status not in (select value from synonymdomain where domainid = 'WOSTATUS' and maxvalue in ('CAN','CLOSE','COMP'))

    ------------------------------
    Omar Abou Ammar
    Senior Technical Consultant
    eSolutions FZ-LLC
    Dubai
    ------------------------------

    Original Message


Related Content