- Maximo status tables are helpful to track the status changes and to understand business flow.
- Querying the status tables for specific record is simple, by directly passing record key as parameter (WONUM, PONUM, INVOICENUM etc.)
- However, retrieving the previous/next status of specific status for bulk of records can be tricky.
Here are few queries, that can be useful in such scenarios.
- Last Status
- Second Last Status
- Next Status of Specific Status
- Previous Status of Specific Status
Queries are given in MS-SQL and Oracle format.
MS-SQL:
Last Status:
select ws1.wonum [WO#], ws1.changedate,ws1.status [Max-Status],ws1.siteid,ws1.changeby,ws1.memo
from wostatus ws1 where ws1.wonum in ('W0001','W0002')
and ws1.changedate=(select max(changedate) from wostatus where wonum=ws1.wonum)
Second Last Status:
select ws1.wonum [WO#], ws1.changedate,ws1.status [Previous Status],ws1.wonum,ws1.siteid,ws1.changeby,ws1.memo
from wostatus ws1 where ws1.wonum in ('W0001','W0002')
and ws1.changedate =
(select min(a.changedate) from (select top 2 changedate from wostatus where wonum=ws1.wonum
order by changedate desc) a);
Next Status of Specific Status:
select wo.wonum [WO#],wo.status 'Current Status',ws1.status as '<StatusXYZ>',
ws1.changedate 'StatusXYZ.ChangeDate',
ws2.status 'Next-Status of XYZ',ws2.changedate 'Next-Status.ChangeDate'
from workorder wo
inner join wostatus ws1 on wo.wonum=ws1.wonum and wo.siteid=ws1.siteid
and ws1.status='INPRG' --ws1.status='<StatusXYZ>'
inner join wostatus ws2 on ws1.wonum=ws2.wonum and ws1.status!=ws2.status and ws2.changedate>ws1.changedate
and ws2.changedate=(select min(changedate) from wostatus
where wonum=ws1.wonum and siteid=ws1.siteid and changedate>ws1.changedate)
where wo.statusdate >= '01-MAR-2025'
order by ws1.changedate desc;
Previous Status of Specific Status:
select wo.wonum,wo.status 'CurrentStatus',ws1.status as '<StatusXYZ>',ws1.changedate 'StatusXYZ.ChangeDate',
ws2.status 'Previous-Status of XYZ',ws2.changedate 'Previous-Status.ChangeDate',*
from Maximo7.dbo.workorder wo (nolock)
inner join Maximo7.dbo.wostatus ws1 (nolock) on wo.wonum=ws1.wonum and wo.siteid=ws1.siteid
and ws1.status='INPRG' --ws1.status='<StatusXYZ>'
inner join wostatus ws2 on ws1.wonum=ws2.wonum and ws1.status!=ws2.status and ws2.changedate<ws1.changedate
and ws2.changedate=(select max(changedate) from Maximo7.dbo.wostatus (nolock)
where wonum=ws1.wonum and siteid=ws1.siteid and changedate<ws1.changedate)
where wo.statusdate >= '01-MAR-2025'
order by ws1.changedate desc;
Oracle:
Same Queries are given in Oracle format as below.
--Last Status--
select ws1.wonum WO#, ws1.changedate,ws1.status MaxStatus,ws1.siteid,ws1.changeby,ws1.memo
from wostatus ws1 where ws1.wonum in ('W00001','W00002','W00003')
and ws1.changedate=(select max(changedate) from wostatus where wonum=ws1.wonum);
--Second Last Status--
select ws1.wonum WO#,ws1.changedate,ws1.status PreviousStatus,ws1.wonum,ws1.siteid,ws1.changeby,ws1.memo
from wostatus ws1 where ws1.wonum in ('W00001','W00002','W00003')
and ws1.changedate = (select min(changedate) from (
select changedate from wostatus where wonum=ws1.wonum and siteid=ws1.siteid order by changedate desc
fetch FIRST 2 rows only));
--Next Status of Specific Status---
select wo.wonum WO#,wo.status "Current-WO.Status",ws1.status as "Status-XYZ",
ws1.changedate "XYZ-StatusDate",
ws2.status "XYZ-Next-Status",ws2.changedate "Next-StatusDate"
from workorder wo
inner join wostatus ws1 on wo.wonum=ws1.wonum and wo.siteid=ws1.siteid
and ws1.status='APPR' --ws1.status='<StatusXYZ>'
inner join wostatus ws2 on ws1.wonum=ws2.wonum and ws1.status!=ws2.status and ws2.changedate>ws1.changedate
and ws2.changedate=(select min(changedate) from wostatus where wonum=ws1.wonum and siteid=ws1.siteid
and changedate>ws1.changedate)
where wo.statusdate >= '01-MAR-2025' order by ws1.changedate;
--Previous Status of Specific Status---
select wo.wonum WO#,wo.status "Current-WO.Status",ws1.status as "Status-XYZ",
ws1.changedate "XYZ-StatusDate",
ws2.status "XYZ-Previous-Status",ws2.changedate "Previous-StatusDate"
from workorder wo
inner join wostatus ws1 on wo.wonum=ws1.wonum and wo.siteid=ws1.siteid
and ws1.status='APPR' --ws1.status='<StatusXYZ>'
inner join wostatus ws2 on ws1.wonum=ws2.wonum and ws1.status!=ws2.status and ws2.changedate<ws1.changedate
and ws2.changedate= (select max(changedate) from wostatus where wonum=ws1.wonum and siteid=ws1.siteid
and changedate<ws1.changedate )
where wo.statusdate >= '01-MAR-2025'
order by ws1.changedate;