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.
Importance of Measuring Reactive Maintenance
Definitions first:
Proactive maintenance could be defined as routine maintenance, e.g. work performed to maintain the asset & functional operation (includes PM and CM). This proactive maintenance should also have been scheduled (ideally in a weekly schedule).
Conversely, reactive maintenance includes corrective actions that are performed in response to some event, e.g. an unplanned situation which requires attention "right away". This work may be categorized as emergency or urgent. This reactive maintenance event may also involve a functional failure. Was this partial or full? A partial functional failure would be a reduction in capability, whereas, a full functional failure would be a complete loss of function.
If you aren't measuring reactive maintenance across all the work groups then you may be missing one of the most significant measures for judging work and asset performance. What if a particular plant system was encountering a particularly large amount of reactive maintenance? What if one work group (or site) was more reactive than another? What if you organization was more reactive than competitors?
The key point, however, is for your site to have these definitions written down and agreed to because organizations seeking continuous improvement need to be able to measure reactive maintenance. And once you can agree to your definition (which is half the battle) then you need to create the SQL statement. It is at this point that you may discover that this SQL cannot be written for some reason, or, the data you are collecting may not be accurate enough to generate this output, or, your EAM processes are inadequate. At least then you will know what is missing from your EAM system and hopefully how to remedy the situation.
w/br
John Reeve
Manager, Practice Leader Maintenance and Reliability Solutions
Cell: 423 314 1312
http://www.linkedin.com/pub/john-reeve/11/644/9b2
Hi,
We define reactive work as any priority 1 or 2 work orders. Priority 1 means a prodcution line or critical asset was down. Priority 2 means line or asset failure was imminent and unplanned work was performed.
We actually look at the total hours in Priorities 1 and 2 against priorities 3, 4 and 5. We started with a low target of no more than 20% priority 1 and 2 and now have th KPI at 10%.
It really did help the sites see how much unplanned work was being done and put in the resources to get that down.
The query and reports are not that easy to do. I have a stored procedure running each night to make the calculations and store them in a custom table. The report runs against the data in that table. Otherwise the report just takes too long to run. I've listed those below for SQL Server. Notice in the table that the fields for percentages are all calculated from other fields in the table.
Dave Bone
STORED PROCEDURE
USE [maxprod]
GO
/****** Object: StoredProcedure [dbo].[OSC_KPI_WO_PRIORITY_Update] Script Date: 02/13/2012 10:12:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[OSC_KPI_WO_PRIORITY_Update]
AS
Delete from OSC_KPI_WO_PRIORITY
Declare
@fiscalyr as varchar(8)
set @fiscalyr = (Select distinct LEFT(f.financialperiod, 4)
FROM financialperiods f Where
(f.periodstart <= getdate()) AND (f.periodend > getdate()))
/*Insert the hours records*/
Insert into OSC_KPI_WO_PRIORITY (orgid,siteid,poolperiod,periodstart,periodend,p12maint,p12ops,
p345,p12,p1,p2,p3,p4,p5,pt)
(SELECT distinct w.orgid+'HR' as orgid, w.siteid, f.financialperiod as poolperiod, periodstart, periodend,
CAST(sum(CASE WHEN ((w.status <> 'CAN') and (wopriority = '1' or wopriority = '2') and (FAILURECODE <> 'NORMALOPS' or FAILURECODE is null))
THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p12maint,
CAST(sum(CASE WHEN ((w.status <> 'CAN') and (wopriority = '1' or wopriority = '2') and (FAILURECODE = 'NORMALOPS'))
THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p12ops,
CAST(sum(CASE WHEN ((w.status <> 'CAN') and (wopriority = '3' or wopriority = '4' or wopriority = '5'))
THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p345,
CAST(sum(CASE WHEN ((wopriority = '1' or wopriority = '2') and (w.status <> 'CAN')) THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p12,
CAST(sum(CASE WHEN (wopriority = '1' and w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p1,
CAST(sum(CASE WHEN (wopriority = '2' and w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p2,
CAST(sum(CASE WHEN (wopriority = '3' and w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p3,
CAST(sum(CASE WHEN (wopriority = '4' and w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p4,
CAST(sum(CASE WHEN (wopriority = '5' and w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p5,
CAST(sum(CASE WHEN (w.status <> 'CAN') THEN (.00001+IsNull(t.REGULARHRS,0)+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,4)) AS pT
FROM workorder w
left join labtrans t on t.transtype = 'WORK' and t.refwo = w.wonum and t.orgid = w.orgid and t.siteid = w.siteid
join financialperiods f on f.orgid = w.orgid and (w.reportdate >= f.periodstart and w.reportdate < f.periodend)
WHERE w.status <> 'CAN' and wopriority > 0
and (w.woclass = 'WORKORDER' or w.woclass = 'ACTIVITY')
and (left(f.financialperiod,4) >= @fiscalyr-1)
GROUP BY w.orgid, w.siteid, f.financialperiod, periodstart, periodend
having sum(CASE WHEN (w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) > 0
and count(CASE WHEN (w.status <> 'CAN') THEN wopriority ELSE null END) > 0)
order by orgid, w.siteid, f.financialperiod
Declare
@fiscalyrWO as varchar(8)
set @fiscalyrWO = (Select distinct LEFT(f.financialperiod, 4)
FROM financialperiods f Where
(f.periodstart <= getdate()) AND (f.periodend > getdate()))
/*Insert the WO count records*/
Insert into OSC_KPI_WO_PRIORITY (orgid,siteid,poolperiod,periodstart,periodend,
p12maintcount,p12opscount,p345count,p12count,p1count,p2count,p3count,p4count,p5count,ptcount)
(SELECT distinct w.orgid, w.siteid, f.financialperiod as poolperiod, periodstart, periodend,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '1' or wopriority = '2') and (FAILURECODE <> 'NORMALOPS' or FAILURECODE is null))
THEN wopriority ELSE null END) AS p12maintcount,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '1' or wopriority = '2') and (FAILURECODE = 'NORMALOPS'))
THEN wopriority ELSE null END) AS p12opscount,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '3' or wopriority = '4' or wopriority = '5'))
THEN wopriority ELSE null END) AS p345count,
count(CASE WHEN ((wopriority = '1' or wopriority = '2') and (w.status <> 'CAN'))
THEN wopriority ELSE null END) AS p12count,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '1')) THEN wopriority ELSE null END) AS p1count,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '2')) THEN wopriority ELSE null END) AS p2count,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '3')) THEN wopriority ELSE null END) AS p3count,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '4')) THEN wopriority ELSE null END) AS p4count,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '5')) THEN wopriority ELSE null END) AS p5count,
count(CASE WHEN (w.status <> 'CAN') THEN wopriority ELSE null END) AS pTcount
FROM workorder w
left join locations l on l.location = w.location and l.orgid = w.orgid and l.siteid = w.siteid
join financialperiods f on f.orgid = w.orgid and (w.reportdate >= f.periodstart and w.reportdate < f.periodend)
left join labtrans t on t.transtype = 'WORK' and t.refwo = w.wonum and t.orgid = w.orgid and t.siteid = w.siteid
WHERE w.status <> 'CAN' and wopriority > 0
and (istask = 0) and (w.woclass = 'WORKORDER' or w.woclass = 'ACTIVITY')
and (left(f.financialperiod,4) >= @fiscalyrWO-1)
GROUP BY w.orgid, w.siteid, f.financialperiod, periodstart, periodend
having sum(CASE WHEN (w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) > 0
and count(CASE WHEN (w.status <> 'CAN') THEN wopriority ELSE null END) > 0)
order by w.orgid, w.siteid, f.financialperiod
CUSTOM TABLE
USE [maxprod]
GO
/****** Object: Table [dbo].[OSC_KPI_WO_PRIORITY] Script Date: 02/13/2012 10:19:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OSC_KPI_WO_PRIORITY](
[orgid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[siteid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[poolperiod] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[periodstart] [smalldatetime] NULL,
[periodend] [smalldatetime] NULL,
[p12maint] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p12maint] DEFAULT ((0)),
[p12ops] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p12ops] DEFAULT ((0)),
[p345] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p345] DEFAULT ((0)),
[p12] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p12] DEFAULT ((0)),
[p1] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p1] DEFAULT ((0)),
[p2] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p2] DEFAULT ((0)),
[p3] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p3] DEFAULT ((0)),
[p4] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p4] DEFAULT ((0)),
[p5] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p5] DEFAULT ((0)),
[pt] [decimal](10, 6) NULL,
[p12maintcount] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p12maintcount] DEFAULT ((0)),
[p12opscount] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p12opscount] DEFAULT ((0)),
[p345count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p345count] DEFAULT ((0)),
[p12count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p12count] DEFAULT ((0)),
[p1count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p1count] DEFAULT ((0)),
[p2count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p2count] DEFAULT ((0)),
[p3count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p3count] DEFAULT ((0)),
[p4count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p4count] DEFAULT ((0)),
[p5count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p5count] DEFAULT ((0)),
[ptcount] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_ptcount] DEFAULT ((0)),
[poolyear] AS (left([poolperiod],(4))) PERSISTED,
[p12percal] AS ([p12]/[pt]) PERSISTED,
[p345percal] AS ([p345]/[pt]) PERSISTED,
[p12maintpercal] AS ([p12maint]/[pt]) PERSISTED,
[p12opspercal] AS ([p12ops]/[pt]) PERSISTED,
[p1percal] AS ([p1]/[pt]) PERSISTED,
[p2percal] AS ([p2]/[pt]) PERSISTED,
[p3percal] AS ([p3]/[pt]) PERSISTED,
[p4percal] AS ([p4]/[pt]) PERSISTED,
[p5percal] AS ([p5]/[pt]) PERSISTED
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--- In MAXIMO@yahoogroups.com, "John" <planschd@...> wrote:
>
> Importance of Measuring Reactive Maintenance
>
> Definitions first:
> Proactive maintenance could be defined as routine maintenance, e.g. work performed to maintain the asset & functional operation (includes PM and CM). This proactive maintenance should also have been scheduled (ideally in a weekly schedule).
> Conversely, reactive maintenance includes corrective actions that are performed in response to some event, e.g. an unplanned situation which requires attention "right away". This work may be categorized as emergency or urgent. This reactive maintenance event may also involve a functional failure. Was this partial or full? A partial functional failure would be a reduction in capability, whereas, a full functional failure would be a complete loss of function.
>
> If you aren't measuring reactive maintenance across all the work groups then you may be missing one of the most significant measures for judging work and asset performance. What if a particular plant system was encountering a particularly large amount of reactive maintenance? What if one work group (or site) was more reactive than another? What if you organization was more reactive than competitors?
>
> The key point, however, is for your site to have these definitions written down and agreed to because organizations seeking continuous improvement need to be able to measure reactive maintenance. And once you can agree to your definition (which is half the battle) then you need to create the SQL statement. It is at this point that you may discover that this SQL cannot be written for some reason, or, the data you are collecting may not be accurate enough to generate this output, or, your EAM processes are inadequate. At least then you will know what is missing from your EAM system and hopefully how to remedy the situation.
>
> w/br
> John Reeve
> Manager, Practice Leader Maintenance and Reliability Solutions
> Cell: 423 314 1312
> http://www.linkedin.com/pub/john-reeve/11/644/9b2
>
Thanks Dave.
Your detailed process [below] makes my point which is ...
The SQL output for productivity measurement may not be as simple as you think, eg Select Status, Worktype, Priority, Failurecode ... in a one-line statement and your done.
It looks like your productivity metrics are truly used in actual decision making.
W/br
John Reeve
cell: 423 314 1312
Sent on the Sprint� Now Network from my BlackBerry�
-----Original Message-----
From: "in2data" <in2data@yahoo.com>
Sender: MAXIMO@yahoogroups.com
Date: Mon, 13 Feb 2012 18:22:50
To: <MAXIMO@yahoogroups.com>
Reply-To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Re: Tip of the Day #220
Hi,
We define reactive work as any priority 1 or 2 work orders. Priority 1 means a prodcution line or critical asset was down. Priority 2 means line or asset failure was imminent and unplanned work was performed.
We actually look at the total hours in Priorities 1 and 2 against priorities 3, 4 and 5. We started with a low target of no more than 20% priority 1 and 2 and now have th KPI at 10%.
It really did help the sites see how much unplanned work was being done and put in the resources to get that down.
The query and reports are not that easy to do. I have a stored procedure running each night to make the calculations and store them in a custom table. The report runs against the data in that table. Otherwise the report just takes too long to run. I've listed those below for SQL Server. Notice in the table that the fields for percentages are all calculated from other fields in the table.
Dave Bone
STORED PROCEDURE
USE [maxprod]
GO
/****** Object: StoredProcedure [dbo].[OSC_KPI_WO_PRIORITY_Update] Script Date: 02/13/2012 10:12:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[OSC_KPI_WO_PRIORITY_Update]
AS
Delete from OSC_KPI_WO_PRIORITY
Declare
@fiscalyr as varchar(8)
set @fiscalyr = (Select distinct LEFT(f.financialperiod, 4)
FROM financialperiods f Where
(f.periodstart <= getdate()) AND (f.periodend > getdate()))
/*Insert the hours records*/
Insert into OSC_KPI_WO_PRIORITY (orgid,siteid,poolperiod,periodstart,periodend,p12maint,p12ops,
p345,p12,p1,p2,p3,p4,p5,pt)
(SELECT distinct w.orgid+'HR' as orgid, w.siteid, f.financialperiod as poolperiod, periodstart, periodend,
CAST(sum(CASE WHEN ((w.status <> 'CAN') and (wopriority = '1' or wopriority = '2') and (FAILURECODE <> 'NORMALOPS' or FAILURECODE is null))
THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p12maint,
CAST(sum(CASE WHEN ((w.status <> 'CAN') and (wopriority = '1' or wopriority = '2') and (FAILURECODE = 'NORMALOPS'))
THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p12ops,
CAST(sum(CASE WHEN ((w.status <> 'CAN') and (wopriority = '3' or wopriority = '4' or wopriority = '5'))
THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p345,
CAST(sum(CASE WHEN ((wopriority = '1' or wopriority = '2') and (w.status <> 'CAN')) THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p12,
CAST(sum(CASE WHEN (wopriority = '1' and w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p1,
CAST(sum(CASE WHEN (wopriority = '2' and w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p2,
CAST(sum(CASE WHEN (wopriority = '3' and w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p3,
CAST(sum(CASE WHEN (wopriority = '4' and w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p4,
CAST(sum(CASE WHEN (wopriority = '5' and w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,2)) AS p5,
CAST(sum(CASE WHEN (w.status <> 'CAN') THEN (.00001+IsNull(t.REGULARHRS,0)+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) as Decimal(10,4)) AS pT
FROM workorder w
left join labtrans t on t.transtype = 'WORK' and t.refwo = w.wonum and t.orgid = w.orgid and t.siteid = w.siteid
join financialperiods f on f.orgid = w.orgid and (w.reportdate >= f.periodstart and w.reportdate < f.periodend)
WHERE w.status <> 'CAN' and wopriority > 0
and (w.woclass = 'WORKORDER' or w.woclass = 'ACTIVITY')
and (left(f.financialperiod,4) >= @fiscalyr-1)
GROUP BY w.orgid, w.siteid, f.financialperiod, periodstart, periodend
having sum(CASE WHEN (w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) > 0
and count(CASE WHEN (w.status <> 'CAN') THEN wopriority ELSE null END) > 0)
order by orgid, w.siteid, f.financialperiod
Declare
@fiscalyrWO as varchar(8)
set @fiscalyrWO = (Select distinct LEFT(f.financialperiod, 4)
FROM financialperiods f Where
(f.periodstart <= getdate()) AND (f.periodend > getdate()))
/*Insert the WO count records*/
Insert into OSC_KPI_WO_PRIORITY (orgid,siteid,poolperiod,periodstart,periodend,
p12maintcount,p12opscount,p345count,p12count,p1count,p2count,p3count,p4count,p5count,ptcount)
(SELECT distinct w.orgid, w.siteid, f.financialperiod as poolperiod, periodstart, periodend,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '1' or wopriority = '2') and (FAILURECODE <> 'NORMALOPS' or FAILURECODE is null))
THEN wopriority ELSE null END) AS p12maintcount,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '1' or wopriority = '2') and (FAILURECODE = 'NORMALOPS'))
THEN wopriority ELSE null END) AS p12opscount,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '3' or wopriority = '4' or wopriority = '5'))
THEN wopriority ELSE null END) AS p345count,
count(CASE WHEN ((wopriority = '1' or wopriority = '2') and (w.status <> 'CAN'))
THEN wopriority ELSE null END) AS p12count,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '1')) THEN wopriority ELSE null END) AS p1count,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '2')) THEN wopriority ELSE null END) AS p2count,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '3')) THEN wopriority ELSE null END) AS p3count,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '4')) THEN wopriority ELSE null END) AS p4count,
count(CASE WHEN ((w.status <> 'CAN') and (wopriority = '5')) THEN wopriority ELSE null END) AS p5count,
count(CASE WHEN (w.status <> 'CAN') THEN wopriority ELSE null END) AS pTcount
FROM workorder w
left join locations l on l.location = w.location and l.orgid = w.orgid and l.siteid = w.siteid
join financialperiods f on f.orgid = w.orgid and (w.reportdate >= f.periodstart and w.reportdate < f.periodend)
left join labtrans t on t.transtype = 'WORK' and t.refwo = w.wonum and t.orgid = w.orgid and t.siteid = w.siteid
WHERE w.status <> 'CAN' and wopriority > 0
and (istask = 0) and (w.woclass = 'WORKORDER' or w.woclass = 'ACTIVITY')
and (left(f.financialperiod,4) >= @fiscalyrWO-1)
GROUP BY w.orgid, w.siteid, f.financialperiod, periodstart, periodend
having sum(CASE WHEN (w.status <> 'CAN') THEN (t.REGULARHRS+IsNull(t.PREMIUMPAYHOURS,0)) ELSE 0 END) > 0
and count(CASE WHEN (w.status <> 'CAN') THEN wopriority ELSE null END) > 0)
order by w.orgid, w.siteid, f.financialperiod
CUSTOM TABLE
USE [maxprod]
GO
/****** Object: Table [dbo].[OSC_KPI_WO_PRIORITY] Script Date: 02/13/2012 10:19:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OSC_KPI_WO_PRIORITY](
[orgid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[siteid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[poolperiod] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[periodstart] [smalldatetime] NULL,
[periodend] [smalldatetime] NULL,
[p12maint] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p12maint] DEFAULT ((0)),
[p12ops] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p12ops] DEFAULT ((0)),
[p345] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p345] DEFAULT ((0)),
[p12] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p12] DEFAULT ((0)),
[p1] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p1] DEFAULT ((0)),
[p2] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p2] DEFAULT ((0)),
[p3] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p3] DEFAULT ((0)),
[p4] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p4] DEFAULT ((0)),
[p5] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p5] DEFAULT ((0)),
[pt] [decimal](10, 6) NULL,
[p12maintcount] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p12maintcount] DEFAULT ((0)),
[p12opscount] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p12opscount] DEFAULT ((0)),
[p345count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p345count] DEFAULT ((0)),
[p12count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p12count] DEFAULT ((0)),
[p1count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p1count] DEFAULT ((0)),
[p2count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p2count] DEFAULT ((0)),
[p3count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p3count] DEFAULT ((0)),
[p4count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p4count] DEFAULT ((0)),
[p5count] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_p5count] DEFAULT ((0)),
[ptcount] [decimal](5, 0) NULL CONSTRAINT [DF_OSC_KPI_WO_PRIORITY_ptcount] DEFAULT ((0)),
[poolyear] AS (left([poolperiod],(4))) PERSISTED,
[p12percal] AS ([p12]/[pt]) PERSISTED,
[p345percal] AS ([p345]/[pt]) PERSISTED,
[p12maintpercal] AS ([p12maint]/[pt]) PERSISTED,
[p12opspercal] AS ([p12ops]/[pt]) PERSISTED,
[p1percal] AS ([p1]/[pt]) PERSISTED,
[p2percal] AS ([p2]/[pt]) PERSISTED,
[p3percal] AS ([p3]/[pt]) PERSISTED,
[p4percal] AS ([p4]/[pt]) PERSISTED,
[p5percal] AS ([p5]/[pt]) PERSISTED
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--- In MAXIMO@yahoogroups.com, "John" <planschd@...> wrote:
>
> Importance of Measuring Reactive Maintenance
>
> Definitions first:
> Proactive maintenance could be defined as routine maintenance, e.g. work performed to maintain the asset & functional operation (includes PM and CM). This proactive maintenance should also have been scheduled (ideally in a weekly schedule).
> Conversely, reactive maintenance includes corrective actions that are performed in response to some event, e.g. an unplanned situation which requires attention "right away". This work may be categorized as emergency or urgent. This reactive maintenance event may also involve a functional failure. Was this partial or full? A partial functional failure would be a reduction in capability, whereas, a full functional failure would be a complete loss of function.
>
> If you aren't measuring reactive maintenance across all the work groups then you may be missing one of the most significant measures for judging work and asset performance. What if a particular plant system was encountering a particularly large amount of reactive maintenance? What if one work group (or site) was more reactive than another? What if you organization was more reactive than competitors?
>
> The key point, however, is for your site to have these definitions written down and agreed to because organizations seeking continuous improvement need to be able to measure reactive maintenance. And once you can agree to your definition (which is half the battle) then you need to create the SQL statement. It is at this point that you may discover that this SQL cannot be written for some reason, or, the data you are collecting may not be accurate enough to generate this output, or, your EAM processes are inadequate. At least then you will know what is missing from your EAM system and hopefully how to remedy the situation.
>
> w/br
> John Reeve
> Manager, Practice Leader Maintenance and Reliability Solutions
> Cell: 423 314 1312
> http://www.linkedin.com/pub/john-reeve/11/644/9b2
>