Maximo Open Forum

 View Only

 Calculate Duration between status dates

  • Reporting
Rob Kelpe's profile image
Rob Kelpe posted 02-03-2021 15:46
Hello All, 

I am trying to calculate the duration in days/hours between status dates, for example WAPPR->APPR, APPR->SCHD, etc. in an ad hoc report. Looking for a way to do this within Maximo, as opposed to having to export to excel, etc. 

Thank You, 

-Rob
Steven Shull's profile image
Steven Shull
Doing it in a QBR would require it to be stored in Maximo persistently or you'd have to export the report and modify it in BIRT. Are you looking for time between specific progressions like you called out or are you looking just between the most recent status before it? For example, if a WO went:

WAPPR->APPR->WMATL->SCHD->INPRG

Would you want to calculate WAPPR->APPR, SCHD->INPRG, and APPR->WMATL or would you want to only calculate from APPR->SCHD? It's not too difficult to get the status immediately preceding it but gets tricky when you're looking for specific progression rules as Maximo allows you to hop around. You could start in APPR for example, go back to WAPPR, and then go back to APPR. If you calculate WAPPR->APPR and look at the first time it was approved you would have a negative calculation but if you look at the latest status change it might not be a good comparison either.
Rob Kelpe's profile image
Rob Kelpe
Hi Steven, 

Thanks for replying. I don't need it between every status, but I would like it between say WAPPR->APPR and SCHD->COMP. I don't think I could necessarily pick and Choose like that, though. It would be all or nothing, right?
Tim Ferrill's profile image
Tim Ferrill
You could potentially add a couple of date fields to the WORKORDER object and populate them via Formulas or Automation Scripts. The tricky thing becomes how to handle scenarios where you hit a particular status more than once.
Christopher Winston's profile image
Christopher Winston
I think you should consider a 3rd party reporting tool, or BIRT if you can build there. QBR works well when you are looking at different fields on the same record, but you are looking at different records in the WOSTATUS table. If you look at say work order 1002 (Rebuild Feedwater Pump) in maxdemo, the status changes become interesting:

Status Status Date Changed By
APPR 2/27/03 10:48 AM LIBERI
WAPPR 2/27/03 10:45 AM LIBERI
APPR 2/27/03 10:04 AM SMITH
WAPPR 12/31/98 5:59 AM MAXIMO

This can be more cumbersome to analyze, but even without the multiple approvals, you are still dealing with different records that relate to a single work order record.
Rob Kelpe's profile image
Rob Kelpe
Thank you all for your responses. I think I figured out a way to do this in Tableau, as it doesn't seem possible to do in QBR.
Alex Walter's profile image
Alex Walter
We've used the LEAD() and LAG() functions in Oracle / SQL Server to calculate timespan information between rows. Mostly this has been used in KPIs, but the query syntax could absolutely be used in a BIRT report.

Oracle: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions070.htm
SQL Server: https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql

I agree with what the other responders have said though about comparing status change dates. That's something to watch out for.

Hope this helps,
Alex
Brian Swanson's profile image
Brian Swanson
HI Rob,
This is something I did for a result set and a KPI, perhaps you could use parts of it to get what you need.  This looks at all workorders generated for the previous month that I compare the status change and the change took between 3 and 5 days.  This is in SQL Server and you would substitute "YOURSTATUS with the two status you want.  Hope it helps.

select * from workorder where wonum in
(select a.wonum from wostatus a, wostatus b where a.wonum = b.wonum and a.wostatusid
in (Select max(wostatusid) from wostatus mwp where mwp.wonum=a.wonum and mwp.status="YOURSTATUS") and b.wostatusid in
(Select min(wostatusid) from wostatus mwp where mwp.wonum=b.wonum and mwp.status="YOURSTATUS")
and a.wonum in (select wonum from workorder where ((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0
and reportdate > datefromparts(year(dateadd(month,-1,getdate())),month(dateadd(month,-1,getdate())),1)
and reportdate < dateadd(day,-3,cast(datefromparts(year(getdate()),month(getdate()),1) as datetime)))) and
(-DATEDIFF(day, a.changedate , b.changedate)) > 3 and (-DATEDIFF(day, a.changedate , b.changedate)) <= 5)
Wes Williams's profile image
Wes Williams
​Hi Rob, have you tried creating a view? Try something like this; I use this combined with another view. Just do the math for each row you could even add
select(Select review - draft, draft - review from (MR_Metrics) etc. to get the duration. Works great in Tableau as well. (Just identify the view in Maximo, System Configuration-> Database Configuration.


CREATE OR REPLACE FORCE VIEW MAXIMO.MR_METRICS
(
   MRNUM,
   DRAFT,
   REVIEW,
   REVIEWED,
   ASSIGNED,
   GSCFINAL,
   APPROVED
)
   BEQUEATH DEFINER
AS
     SELECT mrstatus.mrnum,
            MIN (draft.changedate) draft,
            MIN (review.changedate) review,
            MIN (reviewed.changedate) reviewed,
            MIN (assigned.changedate) assigned,
            MIN (final.changedate) GSCFINAL,
            MIN (appr.changedate) approved
       FROM mrstatus
            LEFT JOIN mrstatus draft
               ON DRAFT.MRNUM = mrstatus.mrnum AND draft.status = 'DRAFT'
            LEFT JOIN mrstatus review
               ON review.MRNUM = mrstatus.mrnum AND review.status = 'REVIEW'
            LEFT JOIN mrstatus assigned ON assigned.MRNUM = mrstatus.mrnum
            LEFT JOIN mrstatus reviewed
               ON     reviewed.MRNUM = mrstatus.mrnum
                  AND review.status = 'REVIEWED'
            LEFT JOIN mrstatus assigned
               ON     assigned.MRNUM = mrstatus.mrnum
                  AND assigned.status = 'ASSIGNED'
            LEFT JOIN mrstatus final
               ON final.mrnum = mrstatus.mrnum AND final.status = 'GSCFINAL'
            LEFT JOIN mrstatus appr
               ON appr.mrnum = mrstatus.mrnum AND appr.status = 'APPR'
      WHERE mrstatus.siteid = 'XYZ' AND mrstatus.changedate >  add_months(trunc(sysdate,'YEAR'),-12)
   GROUP BY mrstatus.mrnum;
​
Adam Bearden's profile image
Adam Bearden

Rob,

I have a statement from my tech that will calculate the hours/days between each status.  Statement works (Oracle) and will list each status of each work order.  Now, if you can turn this into a where clause for me, we both may have our answer?

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID