Maximo Open Forum

 View Only
Expand all | Collapse all

Calculate the Downtime of asset with Status code is ('INSTALL','REMOVE')

  • 1.  Calculate the Downtime of asset with Status code is ('INSTALL','REMOVE')

    Posted 07-11-2022 09:53

    Hello Experts,

    We have a BIRT report requirement to calculate the Asset downtime between the status code ('INSTALL', 'REMOVE') from the asset status table. We have the below scenarios to check.

    Scenario 1:

    Asset: Install Date - Yes
                 Remove Date - Null [Then will calculate the downtime (Current date time - Change date(Install)]

    Scenario 2:
    Asset: Install Date - Yes
                 Remove Date - Yes  [ Then Change date(Remove)-Change date(Install)]

    Scenario 3:
    Asset: Install Date - Yes
                Remove Date - Yes  
                Install Date - Yes 

    Scenario 4:
    Asset: Install Date - Yes
                Remove Date - Yes  
                Install Date - Yes
                Remove Date - Yes
                Install Date - Yes
                Remove Date - Yes

    I need help calculating Scenario 3 and Scenario 4. We are not sure how many entries for an asset with this combination. 

    Note: Out of box Asset time data will not be used for this calculation part.
    Thanks for your help in advance. 

    Regards,

    Karthikeyan


    #Reporting

    ------------------------------
    Karthikeyan K
    IBM India Private Ltd
    ------------------------------


  • 2.  RE: Calculate the Downtime of asset with Status code is ('INSTALL','REMOVE')

    Posted 07-12-2022 11:37
    You are going to start in a regular SQL tool (e.g. DBeaver) with a query on asset status table for asset status code of 'INSTALL'. Then you are going to add a join to asset status for the same asset where the status code is 'REMOVE' and the status date is the biggest one that is less than the status date for your install record. Because you will have the asset status table in your query twice, you will need to give an alias to at least one of them. I would probably use aliases of "installstatus" and "removestatus".

    ------------------------------
    Jason Uppenborn
    Cohesive
    ------------------------------