Maximo Open Forum

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
------------------------------

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
------------------------------