Maximo has a well known feature to display the history of where an asset was located. It is accessible from View Asset Move History from the Asset application.
In the following example the asset DS01-A-002 was created in location DS01-S-SA-01 and then moved to other two locations.
The data displayed in this dialog is stored in the ASSETTRANS database table.
By querying this table we can answer questions like this:
- Where was my asset located at a specific point in time?
- What assets were in a specific location at a specific point in time?
Now lets start reading date from the ASSETTRANS table for asset DS01-A-002. We will get the same results displayed in the dialog above.
select assetnum, toloc, datemoved
from assettrans
where assetnum='DS01-A-002'
order by datemoved;
If we want to know where the asset was the 20th of April we need to know the largest DATEMOVED before that date.
select assetnum, max(datemoved)
from assettrans
where datemoved<TO_TIMESTAMP('2019-04-20', 'YYYY-MM-DD')
and assetnum='DS01-A-002'
group by assetnum;
Now we can join this result in the following way to retrieve the list of asset's locations 'as of' 20th of April at 11AM.
select a.assetnum, a.siteid, a.toloc
from assettrans a
join
(select assetnum, siteid, max(datemoved) d
from assettrans
where datemoved<TO_TIMESTAMP('2019-04-20 11:00:00', 'YYYY-MM-DD HH24:MI:SS')
group by assetnum, siteid
) h on h.assetnum=a.assetnum and h.siteid=a.siteid and h.d=a.datemoved
order by a.assetnum;
We can now see that asset DS01-A-002 was located in DS01-S-SA-02 as expected. The query includes all the assets and can be filtered by asset or location.
Note that in this last query we have added the SITEID field since ASSETNUM/SITEID is the primary key of the ASSET table.
Labels: advanced, sql
