Maximo Open Forum

Hello all, I have now been set up with an SQL tool to run database queries.  

• Is it possible to run an SQL that will give me the YTD Labor Hours for an asset?
• Also the YTD Material Cost?

Thank you

I am not sure on your maximo version but in maximo 7.6.1.3 ASSETROLLUPSTATUS table can help you get those cost details by preparing SQL summing up respective cost against all WO's for the asset.

Hello all, I have now been set up with an SQL tool to run database queries.  

• Is it possible to run an SQL that will give me the YTD Labor Hours for an asset?
• Also the YTD Material Cost?

Thank you

Hello all, I have now been set up with an SQL tool to run database queries.  

• Is it possible to run an SQL that will give me the YTD Labor Hours for an asset?
• Also the YTD Material Cost?

Thank you

Hi @Danny Richardson 

Good Day, I tried the following approach to get the labor and material details: Since every work order is raised on an asset, to perform that maintenance activity, we need some material, which is tracked in MATUSETRANS, and labor transactions, which are tracked in the LABTRANS table. In Maximo, each table holds the asset number attribute. On that basis, I have prepared a script that gives the YTD cost and labor hours.

SELECT W.ASSETNUM,SUM(COALESCE(L.REGULARHRS ,0)) AS "tOTAL lABOR HOUR",
SUM((COALESCE(M.ACTUALCOST ,0))+(COALESCE(L.LINECOST,0))) AS "TOTAL YTD COST" 
FROM WORKORDER W 
INNER JOIN LABTRANS L ON L.ASSETNUM=W.ASSETNUM 
INNER JOIN MATUSETRANS M ON M.ASSETNUM=W.ASSETNUM 
WHERE W.ASSETNUM='ASSETNUM'
GROUP BY W.ASSETNUM

Hello all, I have now been set up with an SQL tool to run database queries.  

• Is it possible to run an SQL that will give me the YTD Labor Hours for an asset?
• Also the YTD Material Cost?

Thank you

Hello all, I have now been set up with an SQL tool to run database queries.  

• Is it possible to run an SQL that will give me the YTD Labor Hours for an asset?
• Also the YTD Material Cost?

Thank you

#Administration
#Analytics
#Assets
#EverythingMaximo

------------------------------
Danny Richardson
Maintenance Administration Facilities
Technimark LLC
------------------------------

If you are on old version of Maximo then you have access to 2 Reports in Asset application-

1. Rollup Maintenance Cost
2. Rollup. Maintenance Cost Update.

If you run Asset Cost Rollup report, it will give you Total cost and YTD cost to be updated on the Asset. It calculates it via summing up Cost reported against CLOSED WO of Labor, Services, Tools & Material (LABTRANS, SERVRECTRANS, TOOLTRANS & MATUSETRANS). 

If you want to update the cost after review of report earlier, you can always run 2nd report of Rollup Maintenance Cost Update. Detail SQL can also be found from these reports.

If you are at M7613 then you have select actions available for Asset Cost Rollup available.

------------------------------
Prashant Sharma
Sedin Technologies
Connect with me @ https://www.linkedin.com/in/psharmamaximo/

#IBM Champion 2022
#IBM Champion 2023

Original Message:
Sent: 01-03-2024 10:33
From: Danny Richardson
Subject: SQL to obtain YTD Labor Hrs and YTD Cost per Asset

Hello all, I have now been set up with an SQL tool to run database queries.  

• Is it possible to run an SQL that will give me the YTD Labor Hours for an asset?
• Also the YTD Material Cost?

Thank you

#Administration
#Analytics
#Assets
#EverythingMaximo

------------------------------
Danny Richardson
Maintenance Administration Facilities
Technimark LLC
------------------------------

Hello all, I have now been set up with an SQL tool to run database queries.  

• Is it possible to run an SQL that will give me the YTD Labor Hours for an asset?
• Also the YTD Material Cost?

Thank you

Hi Danny,

It can be used as the basis, just needs tweaking.  Here's what I don't like about the SQL code supplied. 

1. It relies on the work order as the centre of all transactions and that is just not true.  There is a case to be had that an SR has been raised and that the agent starts/stops the timer or time is manually allocated to the SR.  Of course, this causes a labtrans entry (LABTRANS.TICKETID).  Please don't mistake this for raising an ACTIVITY from SR or INCIDENT as they are work order-based.

2. The SQL has no linkage from the work order to the transaction tables i.e. labtrans.refwo, matusetrans.refwo.  I would not reference the work order at all, rather only involve the asset, labtrans and matusetrans all linked via the assetnum and siteid.

3. It has not taken into account that you must link using the SITEID even if you have only one site.

4. It only recognises the regular hours of work and has not factored in the premium hours (LABTRANS.PREMIUMPAYHOURS).

5. It has forgotten that you want the YTD cost + hours.  That can be just as simple as year(transdate) = year(sysdate), though the sysdate/getdate()/current_timestamp depends on your database as well as how you get to extract the year.  I do have to mention that there is normally the transdate and actualdate, you have to decide which to use.  When using financial periods, a user can transact on say the 3rd, but have the actual date as the 30th...of the previous month.  This allows for the prior financial period to accumulate the costs in cases where the user forgets to enter the cost on the Friday and chooses to do it on the following Monday.

Asset cost rollup

As pointed out there is the table ASSETROLLUPSTATUS.  I would also like to converse about the YTD figures as these used to get populated whenever someone ran the Asset Cost Rollup report and now there is a System Property that can be enabled to automatically add the YTD that you see on the asset.  However, I need to point out that unless you are running 'something' that runs the Zero YTD menu action (also on Labor and Inventory) it only means that the cost is from when it was last reset.  So be mindful if you're looking at that figure.

Hello all, I have now been set up with an SQL tool to run database queries.  

• Is it possible to run an SQL that will give me the YTD Labor Hours for an asset?
• Also the YTD Material Cost?

Thank you