Maximo List Archive

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.

Tip of the Day #202

From: Jason Verly (2011-10-05 17:11)

Here's the same query for us SQL Server folks. ;-)
select top 10 row_number() Over (order by sum(ACTMATCOST+ACTLABCOST) desc) as RowNum, LOCATION, sum(ACTMATCOST+ACTLABCOST) SUM_AMT from WORKORDER
where (REPORTDATE >= {ts '2011-09-01 00:00:00.000'} and REPORTDATE <= {ts '2011-09-30 23:59:59.000'}) and siteid='ACME'
group by LOCATION order by SUM_AMT desc
You may be surprised on what you find when you run this.

--- In MAXIMO@yahoogroups.com, "John" <planschd@...> wrote:
>
> Sometimes it is helpful to extract data which shows Top 10 costs. And, it may also be necessary to issue a group-by command. In the following example, if you wanted to see costs grouped by Location, it would look like the following: (Oracle database)
>
> SELECT rownum, t.*
> from (select LOCATION, sum(ACTMATCOST+ACTLABCOST) sum_amt from WORKORDER
> where to_char(REPORTDATE,'yyyymmdd') BETWEEN '20110701' AND '20110930'
> group by LOCATION order by SUM_AMT desc, LOCATION) t
> where rownum <= 10;
>
> The above produces output like.
> ROWNUM LOCATION SUM_AMT
> 1 C049 192934.01
> 2 G040 155013.05
> 3 E157 119014.27
> 4 G048 113808.78
> 5 G124 109081.33
> 6 E139 106022.63
>
> and so on.
>
> 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
>

From: planschd (2011-10-05 17:18)

Thanks Jason for SQLserver code.

I also find these commands fascinating. I then take the top level record and drilldown by asking ...
�� What assets are driving this
�� Are there recurring problem codes
�� PM versus CM costs
�� Is one craft dominant?

This type of dialog enables the maintenance organization to get out ahead of reactive maintenance and possibly reduce number of unplanned breakdowns.

Sent on the Sprint� Now Network from my BlackBerry�

-----Original Message-----
From: "Jason Verly" <jason.verly@yahoo.com>
Sender: MAXIMO@yahoogroups.com
Date: Wed, 05 Oct 2011 17:11:04
To: <MAXIMO@yahoogroups.com>
Reply-To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version

Here's the same query for us SQL Server folks. ;-)

select top 10 row_number() Over (order by sum(ACTMATCOST+ACTLABCOST) desc) as RowNum, LOCATION, sum(ACTMATCOST+ACTLABCOST) SUM_AMT from WORKORDER
where (REPORTDATE >= {ts '2011-09-01 00:00:00.000'} and REPORTDATE <= {ts '2011-09-30 23:59:59.000'}) and siteid='ACME'
group by LOCATION order by SUM_AMT desc

You may be surprised on what you find when you run this.

--- In MAXIMO@yahoogroups.com, "John" <planschd@...> wrote:
>
> Sometimes it is helpful to extract data which shows Top 10 costs. And, it may also be necessary to issue a group-by command. In the following example, if you wanted to see costs grouped by Location, it would look like the following: (Oracle database)
>
> SELECT rownum, t.*
> from (select LOCATION, sum(ACTMATCOST+ACTLABCOST) sum_amt from WORKORDER
> where to_char(REPORTDATE,'yyyymmdd') BETWEEN '20110701' AND '20110930'
> group by LOCATION order by SUM_AMT desc, LOCATION) t
> where rownum <= 10;
>
> The above produces output like�.
> ROWNUM LOCATION SUM_AMT
> 1 C049 192934.01
> 2 G040 155013.05
> 3 E157 119014.27
> 4 G048 113808.78
> 5 G124 109081.33
> 6 E139 106022.63
>
> and so on�.
>
> 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
>

From: Shannon Rotz (2011-10-06 01:57)

Hey John: just a question from the peanut gallery!
I find your wording fascinating ... I take it that you have *planned*
breakdowns then?? Is that like planned parenthood??
:) :)
Shannon
-----Original Message-----
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
planschd@yahoo.com
Sent: October-05-11 10:18 AM
To: MAXIMO@yahoogroups.com
Subject: Re: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
Thanks Jason for SQLserver code.
I also find these commands fascinating. I then take the top level record and
drilldown by asking ...
>> What assets are driving this
>> Are there recurring problem codes
>> PM versus CM costs
>> Is one craft dominant?
This type of dialog enables the maintenance organization to get out ahead of
reactive maintenance and possibly reduce number of unplanned breakdowns.
Sent on the SprintR Now Network from my BlackBerryR
-----Original Message-----
From: "Jason Verly" <jason.verly@yahoo.com>
Sender: MAXIMO@yahoogroups.com
Date: Wed, 05 Oct 2011 17:11:04
To: <MAXIMO@yahoogroups.com>
Reply-To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
Here's the same query for us SQL Server folks. ;-)
select top 10 row_number() Over (order by sum(ACTMATCOST+ACTLABCOST) desc)
as RowNum, LOCATION, sum(ACTMATCOST+ACTLABCOST) SUM_AMT from WORKORDER
where (REPORTDATE >= {ts '2011-09-01 00:00:00.000'} and REPORTDATE <= {ts
'2011-09-30 23:59:59.000'}) and siteid='ACME'
group by LOCATION order by SUM_AMT desc
You may be surprised on what you find when you run this.

--- In MAXIMO@yahoogroups.com, "John" <planschd@...> wrote:
>
> Sometimes it is helpful to extract data which shows Top 10 costs. And, it
may also be necessary to issue a group-by command. In the following example,
if you wanted to see costs grouped by Location, it would look like the
following: (Oracle database)
>
> SELECT rownum, t.*
> from (select LOCATION, sum(ACTMATCOST+ACTLABCOST) sum_amt from WORKORDER
> where to_char(REPORTDATE,'yyyymmdd') BETWEEN '20110701' AND '20110930'
> group by LOCATION order by SUM_AMT desc, LOCATION) t
> where rownum <= 10;
>
> The above produces output like..
> ROWNUM LOCATION SUM_AMT
> 1 C049 192934.01
> 2 G040 155013.05
> 3 E157 119014.27
> 4 G048 113808.78
> 5 G124 109081.33
> 6 E139 106022.63
>
> and so on..
>
> 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
>

------------------------------------
Email addresses you might need:
Posting: MAXIMO@yahoogroups.com
Join : MAXIMO-subscribe@yahoogroups.com
Leave : MAXIMO-unsubscribe@yahoogroups.com
Cry : MAXIMO-owner@yahoogroups.com
Group : http://groups.yahoo.com/group/MAXIMOYahoo! Groups Links

From: planschd (2011-10-06 14:38)

The word "planned" in this case means scheduled repairs, on a weekly schedule as opposed to self-inflicted reactive maintenance.

I could have worded it differently.
No worries

The goal is to start analyzing your data.

Sent on the Sprint� Now Network from my BlackBerry�

-----Original Message-----
From: "Shannon Rotz" <shannonrotz@yahoo.ca>
Sender: MAXIMO@yahoogroups.com
Date: Thu, 6 Oct 2011 01:57:35
To: <MAXIMO@yahoogroups.com>
Reply-To: MAXIMO@yahoogroups.com
Subject: RE: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version

Hey John: just a question from the peanut gallery!

I find your wording fascinating ... I take it that you have *planned*
breakdowns then?? Is that like planned parenthood??

:) :)

Shannon

-----Original Message-----
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
planschd@yahoo.com
Sent: October-05-11 10:18 AM
To: MAXIMO@yahoogroups.com
Subject: Re: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version

Thanks Jason for SQLserver code.

I also find these commands fascinating. I then take the top level record and
drilldown by asking ...

>> What assets are driving this

>> Are there recurring problem codes

>> PM versus CM costs

>> Is one craft dominant?

This type of dialog enables the maintenance organization to get out ahead of
reactive maintenance and possibly reduce number of unplanned breakdowns.

Sent on the SprintR Now Network from my BlackBerryR

-----Original Message-----

From: "Jason Verly" <jason.verly@yahoo.com>

Sender: MAXIMO@yahoogroups.com

Date: Wed, 05 Oct 2011 17:11:04

To: <MAXIMO@yahoogroups.com>

Reply-To: MAXIMO@yahoogroups.com

Subject: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version

Here's the same query for us SQL Server folks. ;-)

select top 10 row_number() Over (order by sum(ACTMATCOST+ACTLABCOST) desc)
as RowNum, LOCATION, sum(ACTMATCOST+ACTLABCOST) SUM_AMT from WORKORDER

where (REPORTDATE >= {ts '2011-09-01 00:00:00.000'} and REPORTDATE <= {ts
'2011-09-30 23:59:59.000'}) and siteid='ACME'

group by LOCATION order by SUM_AMT desc

You may be surprised on what you find when you run this.

--- In MAXIMO@yahoogroups.com, "John" <planschd@...> wrote:

>

> Sometimes it is helpful to extract data which shows Top 10 costs. And, it
may also be necessary to issue a group-by command. In the following example,
if you wanted to see costs grouped by Location, it would look like the
following: (Oracle database)

>

> SELECT rownum, t.*

> from (select LOCATION, sum(ACTMATCOST+ACTLABCOST) sum_amt from WORKORDER

> where to_char(REPORTDATE,'yyyymmdd') BETWEEN '20110701' AND '20110930'

> group by LOCATION order by SUM_AMT desc, LOCATION) t

> where rownum <= 10;

>

> The above produces output like..

> ROWNUM LOCATION SUM_AMT

> 1 C049 192934.01

> 2 G040 155013.05

> 3 E157 119014.27

> 4 G048 113808.78

> 5 G124 109081.33

> 6 E139 106022.63

>

> and so on..

>

> 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

>

------------------------------------

Email addresses you might need:
Posting: MAXIMO@yahoogroups.com
Join : MAXIMO-subscribe@yahoogroups.com
Leave : MAXIMO-unsubscribe@yahoogroups.com
Cry : MAXIMO-owner@yahoogroups.com
Group : http://groups.yahoo.com/group/MAXIMOYahoo! Groups Links

From: Shannon Rotz (2011-10-06 16:45)

Awww John! I was just giving you a hard time :)
Shannon
-----Original Message-----
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
planschd@yahoo.com
Sent: October-06-11 7:39 AM
To: MAXIMO@yahoogroups.com
Subject: Re: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
The word "planned" in this case means scheduled repairs, on a weekly
schedule as opposed to self-inflicted reactive maintenance.
I could have worded it differently.
No worries
The goal is to start analyzing your data.
Sent on the SprintR Now Network from my BlackBerryR
-----Original Message-----
From: "Shannon Rotz" <shannonrotz@yahoo.ca>
Sender: MAXIMO@yahoogroups.com
Date: Thu, 6 Oct 2011 01:57:35
To: <MAXIMO@yahoogroups.com>
Reply-To: MAXIMO@yahoogroups.com
Subject: RE: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
Hey John: just a question from the peanut gallery!
I find your wording fascinating ... I take it that you have *planned*
breakdowns then?? Is that like planned parenthood??
:) :)
Shannon
-----Original Message-----
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
planschd@yahoo.com
Sent: October-05-11 10:18 AM
To: MAXIMO@yahoogroups.com
Subject: Re: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
Thanks Jason for SQLserver code.
I also find these commands fascinating. I then take the top level record and
drilldown by asking ...
>> What assets are driving this
>> Are there recurring problem codes
>> PM versus CM costs
>> Is one craft dominant?
This type of dialog enables the maintenance organization to get out ahead of
reactive maintenance and possibly reduce number of unplanned breakdowns.

Sent on the SprintR Now Network from my BlackBerryR
-----Original Message-----
From: "Jason Verly" <jason.verly@yahoo.com>
Sender: MAXIMO@yahoogroups.com
Date: Wed, 05 Oct 2011 17:11:04
To: <MAXIMO@yahoogroups.com>
Reply-To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
Here's the same query for us SQL Server folks. ;-)
select top 10 row_number() Over (order by sum(ACTMATCOST+ACTLABCOST) desc)
as RowNum, LOCATION, sum(ACTMATCOST+ACTLABCOST) SUM_AMT from WORKORDER
where (REPORTDATE >= {ts '2011-09-01 00:00:00.000'} and REPORTDATE <= {ts
'2011-09-30 23:59:59.000'}) and siteid='ACME'
group by LOCATION order by SUM_AMT desc

You may be surprised on what you find when you run this.

--- In MAXIMO@yahoogroups.com, "John" <planschd@...> wrote:
>
> Sometimes it is helpful to extract data which shows Top 10 costs. And, it
may also be necessary to issue a group-by command. In the following example,
if you wanted to see costs grouped by Location, it would look like the
following: (Oracle database)
>
> SELECT rownum, t.*
> from (select LOCATION, sum(ACTMATCOST+ACTLABCOST) sum_amt from WORKORDER
> where to_char(REPORTDATE,'yyyymmdd') BETWEEN '20110701' AND '20110930'
> group by LOCATION order by SUM_AMT desc, LOCATION) t
> where rownum <= 10;
>
> The above produces output like..
> ROWNUM LOCATION SUM_AMT
> 1 C049 192934.01
> 2 G040 155013.05
> 3 E157 119014.27
> 4 G048 113808.78
> 5 G124 109081.33
> 6 E139 106022.63
>
> and so on..
>
> 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
>

------------------------------------
Email addresses you might need:
Posting: MAXIMO@yahoogroups.com
Join : MAXIMO-subscribe@yahoogroups.com
Leave : MAXIMO-unsubscribe@yahoogroups.com
Cry : MAXIMO-owner@yahoogroups.com
Group : http://groups.yahoo.com/group/MAXIMOYahoo! Groups Links

------------------------------------
Email addresses you might need:
Posting: MAXIMO@yahoogroups.com
Join : MAXIMO-subscribe@yahoogroups.com
Leave : MAXIMO-unsubscribe@yahoogroups.com
Cry : MAXIMO-owner@yahoogroups.com
Group : http://groups.yahoo.com/group/MAXIMOYahoo! Groups Links

From: in2data (2011-10-07 16:03)

Hi,
I think John had an unplanned senior moment :-)
"You kids get off my lawn!"
Getting older is better than the alternative :-)
Dave Bone
--- In MAXIMO@yahoogroups.com, "Shannon Rotz" <shannonrotz@...> wrote:
>
> Awww John! I was just giving you a hard time :)
>
>
> Shannon
>
> -----Original Message-----
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
> planschd@...
> Sent: October-06-11 7:39 AM
> To: MAXIMO@yahoogroups.com
> Subject: Re: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
>
> The word "planned" in this case means scheduled repairs, on a weekly
> schedule as opposed to self-inflicted reactive maintenance.
>
>
>
> I could have worded it differently.
>
> No worries
>
>
>
> The goal is to start analyzing your data.
>
>
>
>
>
> Sent on the SprintR Now Network from my BlackBerryR
>
>
>
> -----Original Message-----
>
> From: "Shannon Rotz" <shannonrotz@...>
>
> Sender: MAXIMO@yahoogroups.com
>
> Date: Thu, 6 Oct 2011 01:57:35
>
> To: <MAXIMO@yahoogroups.com>
>
> Reply-To: MAXIMO@yahoogroups.com
>
> Subject: RE: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
>
>
>
> Hey John: just a question from the peanut gallery!
>
>
>
> I find your wording fascinating ... I take it that you have *planned*
>
> breakdowns then?? Is that like planned parenthood??
>
>
>
> :) :)
>
>
>
>
>
>
>
> Shannon
>
>
>
>
>
>
>
> -----Original Message-----
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
>
> planschd@...
>
> Sent: October-05-11 10:18 AM
>
> To: MAXIMO@yahoogroups.com
>
> Subject: Re: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
>
>
>
> Thanks Jason for SQLserver code.
>
>
>
>
>
>
>
> I also find these commands fascinating. I then take the top level record and
>
> drilldown by asking ...
>
>
>
> >> What assets are driving this
>
>
>
> >> Are there recurring problem codes
>
>
>
> >> PM versus CM costs
>
>
>
> >> Is one craft dominant?
>
>
>
>
>
>
>
> This type of dialog enables the maintenance organization to get out ahead of
>
> reactive maintenance and possibly reduce number of unplanned breakdowns.
>
>
>
>
>
>
>
>
>
>
>
> Sent on the SprintR Now Network from my BlackBerryR
>
>
>
>
>
>
>
> -----Original Message-----
>
>
>
> From: "Jason Verly" <jason.verly@...>
>
>
>
> Sender: MAXIMO@yahoogroups.com
>
>
>
> Date: Wed, 05 Oct 2011 17:11:04
>
>
>
> To: <MAXIMO@yahoogroups.com>
>
>
>
> Reply-To: MAXIMO@yahoogroups.com
>
>
>
> Subject: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
>
>
>
>
>
>
>
> Here's the same query for us SQL Server folks. ;-)
>
>
>
>
>
>
>
> select top 10 row_number() Over (order by sum(ACTMATCOST+ACTLABCOST) desc)
>
> as RowNum, LOCATION, sum(ACTMATCOST+ACTLABCOST) SUM_AMT from WORKORDER
>
>
>
> where (REPORTDATE >= {ts '2011-09-01 00:00:00.000'} and REPORTDATE <= {ts
>
> '2011-09-30 23:59:59.000'}) and siteid='ACME'
>
>
>
> group by LOCATION order by SUM_AMT desc
>
>
>
>
>
>
>
>
>
>
>
> You may be surprised on what you find when you run this.
>
>
>
>
>
>
>
>
>
>
>
> --- In MAXIMO@yahoogroups.com, "John" <planschd@> wrote:
>
>
>
> >
>
>
>
> > Sometimes it is helpful to extract data which shows Top 10 costs. And, it
>
> may also be necessary to issue a group-by command. In the following example,
>
> if you wanted to see costs grouped by Location, it would look like the
>
> following: (Oracle database)
>
>
>
> >
>
>
>
> > SELECT rownum, t.*
>
>
>
> > from (select LOCATION, sum(ACTMATCOST+ACTLABCOST) sum_amt from WORKORDER
>
>
>
> > where to_char(REPORTDATE,'yyyymmdd') BETWEEN '20110701' AND '20110930'
>
>
>
> > group by LOCATION order by SUM_AMT desc, LOCATION) t
>
>
>
> > where rownum <= 10;
>
>
>
> >
>
>
>
> > The above produces output like..
>
>
>
> > ROWNUM LOCATION SUM_AMT
>
>
>
> > 1 C049 192934.01
>
>
>
> > 2 G040 155013.05
>
>
>
> > 3 E157 119014.27
>
>
>
> > 4 G048 113808.78
>
>
>
> > 5 G124 109081.33
>
>
>
> > 6 E139 106022.63
>
>
>
> >
>
>
>
> > and so on..
>
>
>
> >
>
>
>
> > 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
>
>
>
> >
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> ------------------------------------
>
>
>
> Email addresses you might need:
>
> Posting: MAXIMO@yahoogroups.com
>
> Join : MAXIMO-subscribe@yahoogroups.com
>
> Leave : MAXIMO-unsubscribe@yahoogroups.com
>
> Cry : MAXIMO-owner@yahoogroups.com
>
> Group : http://groups.yahoo.com/group/MAXIMOYahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> ------------------------------------
>
> Email addresses you might need:
> Posting: MAXIMO@yahoogroups.com
> Join : MAXIMO-subscribe@yahoogroups.com
> Leave : MAXIMO-unsubscribe@yahoogroups.com
> Cry : MAXIMO-owner@yahoogroups.com
> Group : http://groups.yahoo.com/group/MAXIMOYahoo! Groups Links
>

From: Big D (2011-10-07 23:45)

I guess you could say that if you don't take care of your assets you PLAN on them breaking down for sure.
--- In MAXIMO@yahoogroups.com, "Shannon Rotz" <shannonrotz@...> wrote:
>
> Awww John! I was just giving you a hard time :)
>
>
> Shannon
>
> -----Original Message-----
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
> planschd@...
> Sent: October-06-11 7:39 AM
> To: MAXIMO@yahoogroups.com
> Subject: Re: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
>
> The word "planned" in this case means scheduled repairs, on a weekly
> schedule as opposed to self-inflicted reactive maintenance.
>
>
>
> I could have worded it differently.
>
> No worries
>
>
>
> The goal is to start analyzing your data.
>
>
>
>
>
> Sent on the SprintR Now Network from my BlackBerryR
>
>
>
> -----Original Message-----
>
> From: "Shannon Rotz" <shannonrotz@...>
>
> Sender: MAXIMO@yahoogroups.com
>
> Date: Thu, 6 Oct 2011 01:57:35
>
> To: <MAXIMO@yahoogroups.com>
>
> Reply-To: MAXIMO@yahoogroups.com
>
> Subject: RE: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
>
>
>
> Hey John: just a question from the peanut gallery!
>
>
>
> I find your wording fascinating ... I take it that you have *planned*
>
> breakdowns then?? Is that like planned parenthood??
>
>
>
> :) :)
>
>
>
>
>
>
>
> Shannon
>
>
>
>
>
>
>
> -----Original Message-----
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
>
> planschd@...
>
> Sent: October-05-11 10:18 AM
>
> To: MAXIMO@yahoogroups.com
>
> Subject: Re: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
>
>
>
> Thanks Jason for SQLserver code.
>
>
>
>
>
>
>
> I also find these commands fascinating. I then take the top level record and
>
> drilldown by asking ...
>
>
>
> >> What assets are driving this
>
>
>
> >> Are there recurring problem codes
>
>
>
> >> PM versus CM costs
>
>
>
> >> Is one craft dominant?
>
>
>
>
>
>
>
> This type of dialog enables the maintenance organization to get out ahead of
>
> reactive maintenance and possibly reduce number of unplanned breakdowns.
>
>
>
>
>
>
>
>
>
>
>
> Sent on the SprintR Now Network from my BlackBerryR
>
>
>
>
>
>
>
> -----Original Message-----
>
>
>
> From: "Jason Verly" <jason.verly@...>
>
>
>
> Sender: MAXIMO@yahoogroups.com
>
>
>
> Date: Wed, 05 Oct 2011 17:11:04
>
>
>
> To: <MAXIMO@yahoogroups.com>
>
>
>
> Reply-To: MAXIMO@yahoogroups.com
>
>
>
> Subject: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
>
>
>
>
>
>
>
> Here's the same query for us SQL Server folks. ;-)
>
>
>
>
>
>
>
> select top 10 row_number() Over (order by sum(ACTMATCOST+ACTLABCOST) desc)
>
> as RowNum, LOCATION, sum(ACTMATCOST+ACTLABCOST) SUM_AMT from WORKORDER
>
>
>
> where (REPORTDATE >= {ts '2011-09-01 00:00:00.000'} and REPORTDATE <= {ts
>
> '2011-09-30 23:59:59.000'}) and siteid='ACME'
>
>
>
> group by LOCATION order by SUM_AMT desc
>
>
>
>
>
>
>
>
>
>
>
> You may be surprised on what you find when you run this.
>
>
>
>
>
>
>
>
>
>
>
> --- In MAXIMO@yahoogroups.com, "John" <planschd@> wrote:
>
>
>
> >
>
>
>
> > Sometimes it is helpful to extract data which shows Top 10 costs. And, it
>
> may also be necessary to issue a group-by command. In the following example,
>
> if you wanted to see costs grouped by Location, it would look like the
>
> following: (Oracle database)
>
>
>
> >
>
>
>
> > SELECT rownum, t.*
>
>
>
> > from (select LOCATION, sum(ACTMATCOST+ACTLABCOST) sum_amt from WORKORDER
>
>
>
> > where to_char(REPORTDATE,'yyyymmdd') BETWEEN '20110701' AND '20110930'
>
>
>
> > group by LOCATION order by SUM_AMT desc, LOCATION) t
>
>
>
> > where rownum <= 10;
>
>
>
> >
>
>
>
> > The above produces output like..
>
>
>
> > ROWNUM LOCATION SUM_AMT
>
>
>
> > 1 C049 192934.01
>
>
>
> > 2 G040 155013.05
>
>
>
> > 3 E157 119014.27
>
>
>
> > 4 G048 113808.78
>
>
>
> > 5 G124 109081.33
>
>
>
> > 6 E139 106022.63
>
>
>
> >
>
>
>
> > and so on..
>
>
>
> >
>
>
>
> > 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
>
>
>
> >
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> ------------------------------------
>
>
>
> Email addresses you might need:
>
> Posting: MAXIMO@yahoogroups.com
>
> Join : MAXIMO-subscribe@yahoogroups.com
>
> Leave : MAXIMO-unsubscribe@yahoogroups.com
>
> Cry : MAXIMO-owner@yahoogroups.com
>
> Group : http://groups.yahoo.com/group/MAXIMOYahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> ------------------------------------
>
> Email addresses you might need:
> Posting: MAXIMO@yahoogroups.com
> Join : MAXIMO-subscribe@yahoogroups.com
> Leave : MAXIMO-unsubscribe@yahoogroups.com
> Cry : MAXIMO-owner@yahoogroups.com
> Group : http://groups.yahoo.com/group/MAXIMOYahoo! Groups Links
>

From: Shannon Rotz (2011-10-07 19:46)

It's just that when I read John's initial e-mail, I had this mental picture
of somebody saying: "OK - let's make this asset break down tomorrow ."

:)

Shannon

From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
Big D
Sent: October-07-11 4:45 PM
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version

I guess you could say that if you don't take care of your assets you PLAN on
them breaking down for sure.
--- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> , "Shannon
Rotz" <shannonrotz@...> wrote:
>
> Awww John! I was just giving you a hard time :)
>
>
> Shannon
>
> -----Original Message-----
> From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
[mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
Of
> planschd@...
> Sent: October-06-11 7:39 AM
> To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> Subject: Re: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
>
> The word "planned" in this case means scheduled repairs, on a weekly
> schedule as opposed to self-inflicted reactive maintenance.
>
>
>
> I could have worded it differently.
>
> No worries
>
>
>
> The goal is to start analyzing your data.
>
>
>
>
>
> Sent on the SprintR Now Network from my BlackBerryR
>
>
>
> -----Original Message-----
>
> From: "Shannon Rotz" <shannonrotz@...>
>
> Sender: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
>
> Date: Thu, 6 Oct 2011 01:57:35
>
> To: <MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> >
>
> Reply-To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
>
> Subject: RE: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
>
>
>
> Hey John: just a question from the peanut gallery!
>
>
>
> I find your wording fascinating ... I take it that you have *planned*
>
> breakdowns then?? Is that like planned parenthood??
>
>
>
> :) :)
>
>
>
>
>
>
>
> Shannon
>
>
>
>
>
>
>
> -----Original Message-----
>
> From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
[mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
Of
>
> planschd@...
>
> Sent: October-05-11 10:18 AM
>
> To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
>
> Subject: Re: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
>
>
>
> Thanks Jason for SQLserver code.
>
>
>
>
>
>
>
> I also find these commands fascinating. I then take the top level record
and
>
> drilldown by asking ...
>
>
>
> >> What assets are driving this
>
>
>
> >> Are there recurring problem codes
>
>
>
> >> PM versus CM costs
>
>
>
> >> Is one craft dominant?
>
>
>
>
>
>
>
> This type of dialog enables the maintenance organization to get out ahead
of
>
> reactive maintenance and possibly reduce number of unplanned breakdowns.
>
>
>
>
>
>
>
>
>
>
>
> Sent on the SprintR Now Network from my BlackBerryR
>
>
>
>
>
>
>
> -----Original Message-----
>
>
>
> From: "Jason Verly" <jason.verly@...>
>
>
>
> Sender: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
>
>
>
> Date: Wed, 05 Oct 2011 17:11:04
>
>
>
> To: <MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> >
>
>
>
> Reply-To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
>
>
>
> Subject: [MAXIMO List] Re: Tip of the Day #202 - SQL Server version
>
>
>
>
>
>
>
> Here's the same query for us SQL Server folks. ;-)
>
>
>
>
>
>
>
> select top 10 row_number() Over (order by sum(ACTMATCOST+ACTLABCOST) desc)
>
> as RowNum, LOCATION, sum(ACTMATCOST+ACTLABCOST) SUM_AMT from WORKORDER
>
>
>
> where (REPORTDATE >= {ts '2011-09-01 00:00:00.000'} and REPORTDATE <= {ts
>
> '2011-09-30 23:59:59.000'}) and siteid='ACME'
>
>
>
> group by LOCATION order by SUM_AMT desc
>
>
>
>
>
>
>
>
>
>
>
> You may be surprised on what you find when you run this.
>
>
>
>
>
>
>
>
>
>
>
> --- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> , "John"
<planschd@> wrote:
>
>
>
> >
>
>
>
> > Sometimes it is helpful to extract data which shows Top 10 costs. And,
it
>
> may also be necessary to issue a group-by command. In the following
example,
>
> if you wanted to see costs grouped by Location, it would look like the
>
> following: (Oracle database)
>
>
>
> >
>
>
>
> > SELECT rownum, t.*
>
>
>
> > from (select LOCATION, sum(ACTMATCOST+ACTLABCOST) sum_amt from WORKORDER
>
>
>
> > where to_char(REPORTDATE,'yyyymmdd') BETWEEN '20110701' AND '20110930'
>
>
>
> > group by LOCATION order by SUM_AMT desc, LOCATION) t
>
>
>
> > where rownum <= 10;
>
>
>
> >
>
>
>
> > The above produces output like..
>
>
>
> > ROWNUM LOCATION SUM_AMT
>
>
>
> > 1 C049 192934.01
>
>
>
> > 2 G040 155013.05
>
>
>
> > 3 E157 119014.27
>
>
>
> > 4 G048 113808.78
>
>
>
> > 5 G124 109081.33
>
>
>
> > 6 E139 106022.63
>
>
>
> >
>
>
>
> > and so on..
>
>
>
> >
>
>
>
> > 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
>
>
>
> >
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> ------------------------------------
>
>
>
> Email addresses you might need:
>
> Posting: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
>
> Join : MAXIMO-subscribe@yahoogroups.com
<mailto:MAXIMO-subscribe%40yahoogroups.com>
>
> Leave : MAXIMO-unsubscribe@yahoogroups.com
<mailto:MAXIMO-unsubscribe%40yahoogroups.com>
>
> Cry : MAXIMO-owner@yahoogroups.com <mailto:MAXIMO-owner%40yahoogroups.com>
>
> Group : http://groups.yahoo.com/group/MAXIMOYahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> ------------------------------------
>
> Email addresses you might need:
> Posting: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> Join : MAXIMO-subscribe@yahoogroups.com
<mailto:MAXIMO-subscribe%40yahoogroups.com>
> Leave : MAXIMO-unsubscribe@yahoogroups.com
<mailto:MAXIMO-unsubscribe%40yahoogroups.com>
> Cry : MAXIMO-owner@yahoogroups.com <mailto:MAXIMO-owner%40yahoogroups.com>
> Group : http://groups.yahoo.com/group/MAXIMOYahoo! Groups Links
>