Welcome Forums Maximo Issues and Help Maximo 7.x discussions Writing a query for work orders older than 30 days

Tagged: 

  • Author
    Posts
  • #2863

    Tim
    Participant

    Hello, I am attempting to write a query that will give me results of open work orders with a reported date of greater than 30 days and I can’t seem to get the where clause correct. Can someone help me out with the correct language for the SQL statement for this? I’ve found a\n example of a statement that will return the oldest record in the system and have been trying to build it from there. Here is what I have so far:

    ((woclass = ‘WORKORDER’ or woclass = ‘ACTIVITY’) and historyflag = 0
    and istask = 0 and siteid = ‘BATES’) and
    status not in (‘CAN’,’
    HISTORY’,’COMP’,’CLOSE’)
    and reportdate in
    (select min(reportdate) from workorder
    where status not in (‘CAN’
    ,’HISTORY’,’COMP’,’CLOSE’))

    obviously everything after reportdate needs to go and be replaced, but I’m not sure what to replace it with. Help

  • #2902

    bambi
    Moderator

    (
    (woclass = ‘WORKORDER’ or woclass = ‘ACTIVITY’)
    and historyflag = 0 and istask = 0 and siteid = ‘BATES’
    )
    and status not in (‘CAN’,’HISTORY’,’COMP’,’CLOSE’)
    and reportdate <= (getdate()-30)

    Of course, depending on if you are using Oracle or SQL that would be sysdate or getdate…

  • #2932

    George
    Participant

    Add this to your query, just like they said in above response.

    and reportdate <= getdate() -30

You must be logged in to reply to this topic.