This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
Actually Maximo takes care of most of this tracking for you in its PM records you just need a way to get the data out. We are on Maximo 6 and have to create our reports in Actuate eSpreadsheet. I developed a report to do just what you ask using two queries that you should be able to adapt to your system by changing the database name from PZMAX to whatever yours is.
This first query does a compilation of what is overdue by counting all the overdue PMs as ODCOUNT and all Active PMs as PMCOUNT and then in my report I divide ODCOUNT by PMCOUNT to get % Overdue:
SELECT ODCOUNT, PMCOUNT
FROM
(SELECT COUNT(*) AS "ODCOUNT"
FROM
(SELECT PMDATA.PMNUM, PMDATA.PMDESC, PMDATA.ASSETNUM, PMDATA.LASTCOMPDATE, PMDATA.WORKTYPE, CONCAT(PMDATA.FREQUENCY, CONCAT(' ', PMDATA.FREQUNIT)) AS FREQ, (PMDATA.LASTCOMPDATE + (PMDATA.FREQUENCY * PMDATA.MULT*1.2)) AS "OVERDUEDATE"
FROM
(SELECT PM.PMNUM, PM.DESCRIPTION AS "PMDESC", PM.ASSETNUM, PM.LASTCOMPDATE, PM.FREQUENCY, PM.FREQUNIT, PM.WORKTYPE,
CASE PM.FREQUNIT
WHEN 'YEARS' THEN 365
WHEN 'MONTHS' THEN 30
WHEN 'WEEKS' THEN 7
WHEN 'DAYS' THEN 1
END AS MULT
FROM PZMAX.PM PM
WHERE ((PM.SITEID=:SITEID) AND (PM.STATUS='ACTIVE') AND (PM.WORKTYPE<>'EVT') AND (PM.FREQUENCY <> 0))) PMDATA) ODDATA
WHERE ODDATA.OVERDUEDATE < SYSDATE),
(SELECT COUNT(*) AS "PMCOUNT"
FROM PZMAX.PM PM
WHERE ((PM.SITEID=:SITEID) AND (PM.STATUS='ACTIVE') AND (PM.WORKTYPE<>'EVT') AND (PM.FREQUENCY <> 0)))
This second query creates a detailed listing of the PM's and their status the PMDATA.MULT * 1.2 is used to calculate the date a PM is 20% past its scheduled performance. You can adjust your criteria by changing the 1.2 to say 1.1 if you only want 10%:
SELECT PMDATA.PMNUM, PMDATA.PMDESC, PMDATA.ASSETNUM, PMDATA.LASTCOMPDATE, PMDATA.WORKTYPE, CONCAT(PMDATA.FREQUENCY, CONCAT(' ', PMDATA.FREQUNIT)) AS FREQ, (PMDATA.LASTCOMPDATE + (PMDATA.FREQUENCY * PMDATA.MULT*1.2)) AS "OVERDUEDATE"
FROM
(SELECT PM.PMNUM, PM.DESCRIPTION AS "PMDESC", PM.ASSETNUM, PM.LASTCOMPDATE, PM.FREQUENCY, PM.FREQUNIT, PM.WORKTYPE,
CASE PM.FREQUNIT
WHEN 'YEARS' THEN 365
WHEN 'MONTHS' THEN 30
WHEN 'WEEKS' THEN 7
WHEN 'DAYS' THEN 1
END AS MULT
FROM PZMAX.PM PM
WHERE ((PM.SITEID=:SITEID) AND (PM.STATUS='ACTIVE') AND (PM.WORKTYPE<>'EVT') AND (PM.FREQUENCY <> 0))) PMDATA
--
Dave Teece
Reliability Analyst / CMMS Support, CMRP
Salt
Cargill
Hi Dave - Thanks for the SQL. I wrote up something similar yesterday, but I'll see how the data looks in Excel. :)
---In MAXIMO@yahoogroups.com, <david_teece@...> wrote:
Actually Maximo takes care of most of this tracking for you in its PM records you just need a way to get the data out. We are on Maximo 6 and have to create our reports in Actuate eSpreadsheet. I developed a report to do just what you ask using two queries that you should be able to adapt to your system by changing the database name from PZMAX to whatever yours is.
This first query does a compilation of what is overdue by counting all the overdue PMs as ODCOUNT and all Active PMs as PMCOUNT and then in my report I divide ODCOUNT by PMCOUNT to get % Overdue:
SELECT ODCOUNT, PMCOUNT
FROM
(SELECT COUNT(*) AS "ODCOUNT"
FROM
(SELECT PMDATA.PMNUM, PMDATA.PMDESC, PMDATA.ASSETNUM, PMDATA.LASTCOMPDATE, PMDATA.WORKTYPE, CONCAT(PMDATA.FREQUENCY, CONCAT(' ', PMDATA.FREQUNIT)) AS FREQ, (PMDATA.LASTCOMPDATE + (PMDATA.FREQUENCY * PMDATA.MULT*1.2)) AS "OVERDUEDATE"
FROM
(SELECT PM.PMNUM, PM.DESCRIPTION AS "PMDESC", PM.ASSETNUM, PM.LASTCOMPDATE, PM.FREQUENCY, PM.FREQUNIT, PM.WORKTYPE,
CASE PM.FREQUNIT
WHEN 'YEARS' THEN 365
WHEN 'MONTHS' THEN 30
WHEN 'WEEKS' THEN 7
WHEN 'DAYS' THEN 1
END AS MULT
FROM PZMAX.PM PM
WHERE ((PM.SITEID=:SITEID) AND (PM.STATUS='ACTIVE') AND (PM.WORKTYPE<>'EVT') AND (PM.FREQUENCY <> 0))) PMDATA) ODDATA
WHERE ODDATA.OVERDUEDATE < SYSDATE),
(SELECT COUNT(*) AS "PMCOUNT"
FROM PZMAX.PM PM
WHERE ((PM.SITEID=:SITEID) AND (PM.STATUS='ACTIVE') AND (PM.WORKTYPE<>'EVT') AND (PM.FREQUENCY <> 0)))
This second query creates a detailed listing of the PM’s and their status the PMDATA.MULT * 1.2 is used to calculate the date a PM is 20% past its scheduled performance. You can adjust your criteria by changing the 1.2 to say 1.1 if you only want 10%:
SELECT PMDATA.PMNUM, PMDATA.PMDESC, PMDATA.ASSETNUM, PMDATA.LASTCOMPDATE, PMDATA.WORKTYPE, CONCAT(PMDATA.FREQUENCY, CONCAT(' ', PMDATA.FREQUNIT)) AS FREQ, (PMDATA.LASTCOMPDATE + (PMDATA.FREQUENCY * PMDATA.MULT*1.2)) AS "OVERDUEDATE"
FROM
(SELECT PM.PMNUM, PM.DESCRIPTION AS "PMDESC", PM.ASSETNUM, PM.LASTCOMPDATE, PM.FREQUENCY, PM.FREQUNIT, PM.WORKTYPE,
CASE PM.FREQUNIT
WHEN 'YEARS' THEN 365
WHEN 'MONTHS' THEN 30
WHEN 'WEEKS' THEN 7
WHEN 'DAYS' THEN 1
END AS MULT
FROM PZMAX.PM PM
WHERE ((PM.SITEID=:SITEID) AND (PM.STATUS='ACTIVE') AND (PM.WORKTYPE<>'EVT') AND (PM.FREQUENCY <> 0))) PMDATA
--
Dave Teece
Reliability Analyst / CMMS Support, CMRP
Salt
Cargill
I ended up using this and making a Pivot Table in Excel. Your query is useful for PM planning and immediate forecasting... but I think they were more interested in annual performance type scenario.
The query below is for Oracle. pm.pm6 is a custom field for us I think. Otherwise, everything else should work out of the box.
select wo.wonum, wo.description, wo.status, wo.targcompdate, pm.pmnum, pm.pm6 as deptnum, pm.frequency, pm.frequnit, (case
when pm.frequency = 1 and pm.frequnit = 'DAYS' then 1
when pm.frequency = 14 and pm.frequnit = 'DAYS' then 14
when pm.frequency = 30 and pm.frequnit = 'DAYS' then 30
when pm.frequency = 90 and pm.frequnit = 'DAYS' then 90
when pm.frequency = 1 and pm.frequnit = 'WEEKS' then 7
when pm.frequency = 2 and pm.frequnit = 'WEEKS' then 14
when pm.frequency = 1 and pm.frequnit = 'MONTHS' then 30
when pm.frequency = 2 and pm.frequnit = 'MONTHS' then 60
when pm.frequency = 3 and pm.frequnit = 'MONTHS' then 90
when pm.frequency = 4 and pm.frequnit = 'MONTHS' then 120
when pm.frequency = 5 and pm.frequnit = 'MONTHS' then 150
when pm.frequency = 6 and pm.frequnit = 'MONTHS' then 180
when pm.frequency = 12 and pm.frequnit = 'MONTHS' then 365
when pm.frequency = 1 and pm.frequnit = 'YEARS' then 365
when pm.frequency = 2 and pm.frequnit = 'YEARS' then 730
end) freqvalue,
lt.laborcode, lt.regularhrs, lt.finishdate,
lt.finishdate-lag(lt.finishdate) over (partition by pm.pmnum order by pm.pmnum, lt.finishdate) as fd_diff
from workorder wo
join pm on pm.pmnum = wo.pmnum
left join labtrans lt on lt.refwo = wo.wonum
where pm.status = 'ACTIVE'
and wo.siteid = 'FAC'
and wo.istask = 0
and wo.status not in ('INPRG','WSCH')
and wo.targcompdate between to_date('2012-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and to_date('2013-06-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
order by pm.pm6, pm.pmnum, lt.finishdate, wo.targcompdate
---In maximo@yahoogroups.com, <swkim@...> wrote:
Hi Dave - Thanks for the SQL. I wrote up something similar yesterday, but I'll see how the data looks in Excel. :)
---In MAXIMO@yahoogroups.com, <david_teece@...> wrote:
Actually Maximo takes care of most of this tracking for you in its PM records you just need a way to get the data out. We are on Maximo 6 and have to create our reports in Actuate eSpreadsheet. I developed a report to do just what you ask using two queries that you should be able to adapt to your system by changing the database name from PZMAX to whatever yours is.
This first query does a compilation of what is overdue by counting all the overdue PMs as ODCOUNT and all Active PMs as PMCOUNT and then in my report I divide ODCOUNT by PMCOUNT to get % Overdue:
SELECT ODCOUNT, PMCOUNT
FROM
(SELECT COUNT(*) AS "ODCOUNT"
FROM
(SELECT PMDATA.PMNUM, PMDATA.PMDESC, PMDATA.ASSETNUM, PMDATA.LASTCOMPDATE, PMDATA.WORKTYPE, CONCAT(PMDATA.FREQUENCY, CONCAT(' ', PMDATA.FREQUNIT)) AS FREQ, (PMDATA.LASTCOMPDATE + (PMDATA.FREQUENCY * PMDATA.MULT*1.2)) AS "OVERDUEDATE"
FROM
(SELECT PM.PMNUM, PM.DESCRIPTION AS "PMDESC", PM.ASSETNUM, PM.LASTCOMPDATE, PM.FREQUENCY, PM.FREQUNIT, PM.WORKTYPE,
CASE PM.FREQUNIT
WHEN 'YEARS' THEN 365
WHEN 'MONTHS' THEN 30
WHEN 'WEEKS' THEN 7
WHEN 'DAYS' THEN 1
END AS MULT
FROM PZMAX.PM PM
WHERE ((PM.SITEID=:SITEID) AND (PM.STATUS='ACTIVE') AND (PM.WORKTYPE<>'EVT') AND (PM.FREQUENCY <> 0))) PMDATA) ODDATA
WHERE ODDATA.OVERDUEDATE < SYSDATE),
(SELECT COUNT(*) AS "PMCOUNT"
FROM PZMAX.PM PM
WHERE ((PM.SITEID=:SITEID) AND (PM.STATUS='ACTIVE') AND (PM.WORKTYPE<>'EVT') AND (PM.FREQUENCY <> 0)))
This second query creates a detailed listing of the PM’s and their status the PMDATA.MULT * 1.2 is used to calculate the date a PM is 20% past its scheduled performance. You can adjust your criteria by changing the 1.2 to say 1.1 if you only want 10%:
SELECT PMDATA.PMNUM, PMDATA.PMDESC, PMDATA.ASSETNUM, PMDATA.LASTCOMPDATE, PMDATA.WORKTYPE, CONCAT(PMDATA.FREQUENCY, CONCAT(' ', PMDATA.FREQUNIT)) AS FREQ, (PMDATA.LASTCOMPDATE + (PMDATA.FREQUENCY * PMDATA.MULT*1.2)) AS "OVERDUEDATE"
FROM
(SELECT PM.PMNUM, PM.DESCRIPTION AS "PMDESC", PM.ASSETNUM, PM.LASTCOMPDATE, PM.FREQUENCY, PM.FREQUNIT, PM.WORKTYPE,
CASE PM.FREQUNIT
WHEN 'YEARS' THEN 365
WHEN 'MONTHS' THEN 30
WHEN 'WEEKS' THEN 7
WHEN 'DAYS' THEN 1
END AS MULT
FROM PZMAX.PM PM
WHERE ((PM.SITEID=:SITEID) AND (PM.STATUS='ACTIVE') AND (PM.WORKTYPE<>'EVT') AND (PM.FREQUENCY <> 0))) PMDATA
--
Dave Teece
Reliability Analyst / CMMS Support, CMRP
Salt
Cargill