Show records within 15 days of each other.

[tav1035]

I need a report that would show where a REACTIVE workorder (RD) have been generated 15 days or less after a PREVENTIVE MAINTENANCE workorder (PM).
These are all in one table called WORKORDER, but the WORKTYPE would be either PM or RD.

I would also like to group them by ASSETNUM and Sort by REPORTDATE asc.

The results would show like:
WONUM DESC WORKTYPE REPORTDATE
101 Lube bearings PM 1/1/10
102 Bearing making noises RD 1/9/10

I want to suppress single PM's that don't have an RD within 15 days of it.
I want to suppress single RD's that don't have a PM 15 days prior to it.

Here is what I have so far. but can't figure out how to nest the worktype and actual finish date?
Here is what I tried in the select expert({WORKORDER.WORKTYPE} ="PM" and {WORKORDER.ACTFINISH} < {WORKORDER.WORKTYPE} ="RD" and {WORKORDER.ACTFINISH}+15)

The rest of this kinda works if I could suppress like I stated earlier.

Select Expert:
(Not(isNull({WORKORDER.ASSETNUM}))) and
({WORKORDER.ACTFINISH} < {WORKORDER.ACTFINISH}+15) and
{WORKORDER.ISTASK} = 0.00 and
{WORKORDER.HISTORYFLAG} = 1.00 and
{WORKORDER.WORKTYPE} in ["PM", "RD"] and
{WORKORDER.WOEQ9} like "LGB*"


SQL:
SELECT "WORKORDER"."STATUS", "WORKORDER"."WONUM", "WORKORDER"."WORKTYPE", "WORKORDER"."LOCATION", "WORKORDER"."ESTLABHRS", "WORKORDER"."ACTLABHRS", "WORKORDER"."TARGCOMPDATE", "WORKORDER"."REPORTDATE", "WORKORDER"."HISTORYFLAG", "WORKORDER"."ISTASK", "WORKORDER"."DESCRIPTION", "PERSON"."DISPLAYNAME", "WORKORDER"."ACTFINISH", "WORKORDER"."ASSETNUM", "WORKORDER"."WOEQ9"
FROM "MAXIMO"."WORKORDER" "WORKORDER", "MAXIMO"."PERSON" "PERSON"
WHERE ("WORKORDER"."LEAD"="PERSON"."PERSONID" (+)) AND "WORKORDER"."WOEQ9" LIKE 'LGB%' AND "WORKORDER"."ASSETNUM" IS NOT NULL AND "WORKORDER"."ISTASK"=0 AND "WORKORDER"."HISTORYFLAG"=1 AND ("WORKORDER"."WORKTYPE"='PM' OR "WORKORDER"."WORKTYPE"='RD')
ORDER BY "WORKORDER"."ASSETNUM", "WORKORDER"."TARGCOMPDATE"

 

[lbass]

You can't build the date difference into a record selection formula since the comparison would be between different rows of data. Instead go into the section expert->details->suppress->x+2 and enter:

(
(
not onfirstrecord and
{workorder.assetnumber} = previous({workorder.assetnumber})
) and
{workorder.worktype} = "RD" and
previous({workorder.worktype}) = "PM" and
datediff("d",previous({workorder.reportdate}),{workorder.reportdate}) > 15
) or
(
(
not onlastrecord and
{workorder.assetnumber} = next({workorder.assetnumber})
) and
{workorder.worktype} = "PM" and
next({workorder.worktype}) = "RD" and
datediff("d",{workorder.reportdate},next({workorder.reportdate})) > 15
)

I'm not quite sure this will work as you wish--depends upon whether you could have a series of PMs followed by a RD or whether there can be more than one RDs.

-LB

 

[tav1035]

lbass,
I tried you suggestion, but still getting RD's for an asset that has no other records against this asset (never any PM worktypes either). I would like to suppress this type of record.

I also get PM's for an asset that has no other records against this asset (never any PM worktypes either). I would like to suppress this type of record too.
Any other ideas?
Tav

 

[lbass]

distinctcount({workorder.worktype},{workorder.assetnumber}) = 1 or
(
(
not onfirstrecord and
{workorder.assetnumber} = previous({workorder.assetnumber})
) and
{workorder.worktype} = "RD" and
previous({workorder.worktype}) = "PM" and
datediff("d",previous({workorder.reportdate}),{workorder.reportdate}) > 15
) or
(
(
not onlastrecord and
{workorder.assetnumber} = next({workorder.assetnumber})
) and
{workorder.worktype} = "PM" and
next({workorder.worktype}) = "RD" and
datediff("d",{workorder.reportdate},next({workorder.reportdate})) > 15
)

-LB

 

[tav1035]

Lbass, Sorry, I can't get it to suppress so I have tried this in a formula.
Here is what I have so far.

if
{workorder.assetnum} = previous({workorder.assetnum}) and
previous({workorder.worktype}) = "PM" and
({workorder.worktype}) = "RD" and
datediff("d",previous({WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE})<5
or

{workorder.assetnum} = next({workorder.assetnum}) and
next({workorder.worktype}) = "RD" and
({workorder.worktype}) = "PM" and
datediff("d",{WORKORDER.REPORTDATE},next({WORKORDER.REPORTDATE}))<5
then
"Keep"

Then I pulled the formula into my report as a field called show. That gives me visibility of the ones I want to see in my report.

However, I would like to only show the ones that are evaluated as "Keep" and suppress the others.

Here are the results from crystal-
WO# WT STATUS DESC ASSET# REPDATE FINDATE SHOW

539837 RD CLOSE HYD CART KEEPS TRIPING BREAKER 00021450 8/4/05 8/8/05
539840 RD CLOSE HYD CART PUMP PROBLEMS 00021450 8/4/05 8/10/05
15429 RD CLOSE Mechanical Services... 00021450 8/5/05 8/11/05
548255 PM CLOSE Insure Brakes Working ... 00021450 8/6/05 8/25/05
548254 PM CLOSE TEST STAND HYD #075 00021450 8/6/05 8/25/05
548259 PM CLOSE Insure Gauges are Zeroed... 00021450 8/6/05 8/25/05
548258 PM CLOSE Check: Warning Lights... 00021450 8/6/05 8/25/05
548256 PM CLOSE Grease Drive Coupling ... 00021450 8/6/05 8/25/05
548257 PM CLOSE Check for Leaks, Boost ... 00021450 8/6/05 8/25/05
548260 PM CLOSE Check: Wheel Bearings... 00021450 8/6/05 8/25/05 Keep
15697 RD CLOSE Repair Equipment{Req... 00021450 8/9/05 8/15/05 Keep
572942 RD CLOSE HYDRAULIC CART SHUTS... 00021450 8/9/05 8/10/05
1938221 PM CLOSE Grease Drive Coupling ... 00021450 2/1/06 2/27/06
1938220 PM CLOSE Insure Brakes Working ... 00021450 2/1/06 2/27/06
1938219 PM CLOSE TEST STAND HYD #075... 00021450 2/1/06 2/27/06
1938223 PM CLOSE Check: Warning Lights... 00021450 2/1/06 2/27/06
1938222 PM CLOSE Check for Leaks, Boost... 00021450 2/1/06 2/27/06
1938224 PM CLOSE Insure Gauges are Zero... 00021450 2/1/06 2/27/06
1938225 PM CLOSE Check: Wheel Bearings... 00021450 2/1/06 2/27/06

Thanks
tav