Maximo Open Forum

 View Only

 SQL Query to identify WO in COMP with no Actuals

  • Reporting
Kimberly Garcia's profile image
Kimberly Garcia posted 10-07-2022 13:42
I am trying to create a Weekly SQL query based Report to identify instances of Work Orders being completed without Actuals. 
Version 7.6x

This is my attempt, which produces errors.

workorder.reportdate between dateadd( day,-7,getdate( ) ) and dateadd( day,-1,getdate( ) )and (status = 'COMP' and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0)

(status = 'COMP' and istask = 0) and (exists (select 1 from dbo.assignment where ((laborcode is null))
Craig Kokay's profile image
Craig Kokay
First thing with the query is to note that the reportdate is a timestamp, therefore any comparison using getdate() is not the same as that is a date.  If your intention is to actually work from midnight to midnight, you need to work with dates.  One way is to CAST the timestamp field as a date.  In Oracle, I would have used trunc(reportdate).

Next, the assignment table has nothing to do with actuals.  Actuals consist of MATRECTRANS, SERVRECTRANS, TOOLTRANS, and LABTRANS i.e. a transaction has occurred.  So, I've eliminated that from the query.  With that said, that part of the query was not correct as it didn't link the assignment to the workorder to prove that there was an assignment without a work order.  The following is that corrected:

and exists (select 1 from ASSIGNMENT a where (laborcode is null) AND a.WONUM = workorder.WONUM )

Next, "without Actuals"... I've had to assume that as long as there is one transaction is any of the transaction table, that the work order is not one that you're looking for.

So, my query is:

Select from workorder
where
CAST(workorder.reportdate AS date) between CAST(now() AS date) -7 and CAST(now() AS date)-1
and (status = 'COMP' and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0)
AND (
  NOT EXISTS (SELECT 1 FROM MATUSETRANS m WHERE REFWO = workorder.wonum AND m.SITEID = workorder.SITEID)
  OR NOT EXISTS (SELECT 1 FROM SERVRECTRANS s WHERE s.REFWO = workorder.wonum AND s.SITEID = workorder.SITEID)
  OR NOT EXISTS (SELECT 1 FROM LABTRANS l WHERE l.REFWO = workorder.wonum AND l.SITEID = workorder.SITEID)
  OR NOT EXISTS (SELECT 1 FROM TOOLTRANS t WHERE t.REFWO = workorder.wonum AND t.SITEID = workorder.SITEID)
);

Now, of course that didn't answer why the original statement didn't work.  Brackets!  It's that simple.  You didn't have enough.  I've laid this out below:

SELECT * FROM WORKORDER
where
workorder.reportdate between dateadd( day,-7,getdate( ) ) and dateadd( day,-1,getdate( ) )
and (
status = 'COMP'
and (woclass = 'WORKORDER' or woclass = 'ACTIVITY')
and historyflag = 0
and istask = 0
)
AND (
status = 'COMP'
and istask = 0
)
and (
exists (
select 1
from assignment
where (
(
laborcode is NULL
)
)
)
)
Travis Herron's profile image
Travis Herron
Craig has a lot of good stuff in his reply there, especially about making sure you do the report from midnight at the beginning of the week to midnight at the end of the week.

I would, however, question why you are using WORKORDER.REPORTDATE as the basis of this.  That would mean the Work Order would have to have been BOTH reported (created) AND completed "last week."  What if it had been reported 2 months ago, but was completed "last week?"  I would think that should also be included for consideration of whether or not it had actuals.

Also, don't subtract 1 from the 2nd getdate() statement.  For example, if I run this right now, Oct. 10, 2022, 9:09 Central:

SELECT wonum, reportdate 
FROM workorder 
WHERE reportdate BETWEEN CAST(getdate() - 7 AS DATE) and CAST(getdate() - 1 AS DATE) 
ORDER BY reportdate​

The results only include Oct. 3 - Oct. 8.  So dates 3, 4, 5, 6, 7, and 8 -- that's only 6 days; not a full week.



Depending on your company's processes (or if you have users who occasionally hit wrong buttons), it may be possible that a Work Order is both Completed and Closed "last week."  For that reason, I would use the WOSTATUS table to find things that were "completed last week."

Finally, as Craig has alluded, it is unclear if you were asking for "Work Orders completed last week without Actual Labor " or "Work Orders completed last week without any kind of Actuals" -- no actual Labor, Materials, Tools, or Services.  From what you wrote, I think you intended "without Actual Labor" but I'll build upon Craig's response, including "without any kind of Actuals."  If you meant just "without Labor" remove the last three lines that start with AND NOT EXISTS.

This is written for a SQL Server database:

SELECT * 
FROM workorder 
WHERE 
EXISTS (SELECT 1 FROM WOSTATUS ws WHERE wonum = workorder.wonum AND ws.siteid = workorder.siteid 
	AND status = 'COMP' and CAST(changedate AS DATE) BETWEEN CAST(getdate() - 7 AS DATE) AND CAST(getdate() AS DATE)) 
AND (woclass = 'WORKORDER' OR woclass = 'ACTIVITY') AND historyflag = 0 AND istask = 0 
AND (
  NOT EXISTS (SELECT 1 FROM matusetrans m WHERE refwo = workorder.wonum AND m.siteid = workorder.siteid) 
  AND NOT EXISTS (SELECT 1 FROM servrectrans s WHERE s.refwo = workorder.wonum AND s.siteid = workorder.siteid) 
  AND NOT EXISTS (SELECT 1 FROM labtrans l WHERE l.refwo = workorder.wonum AND l.siteid = workorder.siteid) 
  AND NOT EXISTS (SELECT 1 FROM tooltrans t WHERE t.refwo = workorder.wonum AND t.siteid = workorder.siteid)
);



Stevie Holloway's profile image
Stevie Holloway
This is written for Oracle SQL

SELECT *
FROM workorder
WHERE
EXISTS (SELECT 1 FROM WOSTATUS ws WHERE wonum = workorder.wonum AND ws.siteid = workorder.siteid
AND status = 'COMP' and CAST(changedate AS DATE) BETWEEN CAST(sysdate - 7 AS DATE) AND CAST(sysdate AS DATE))
AND (woclass = 'WORKORDER' OR woclass = 'ACTIVITY') AND historyflag = 0 AND istask = 0
AND (
NOT EXISTS (SELECT 1 FROM matusetrans m WHERE refwo = workorder.wonum AND m.siteid = workorder.siteid)
AND NOT EXISTS (SELECT 1 FROM servrectrans s WHERE s.refwo = workorder.wonum AND s.siteid = workorder.siteid)
AND NOT EXISTS (SELECT 1 FROM labtrans l WHERE l.refwo = workorder.wonum AND l.siteid = workorder.siteid)
AND NOT EXISTS (SELECT 1 FROM tooltrans t WHERE t.refwo = workorder.wonum AND t.siteid = workorder.siteid)
);