I am attempting to write a query in maximo that will capture all open work orders with target complete dates prior to the current month. I can't get it to work. any suggestions on how to write the date query so that it eliminates anything with a targcompdate after the 1st of the current month?

 

Thanks!

------------------------------
Shlomo Shvartz
------------------------------

#Maximo
#AssetandFacilitiesManagement

I assume you use oracle database, here is a simple sql query
select * from workorder where istask='0' and historyflag='0' and targcompdate < (select trunc(last_day(sysdate)-1, 'mm') from dual)

------------------------------
SIVATHANU SIVAKUMAR
Asset Management Systems Advisor
------------------------------

The previous response should work for Oracle (and maybe DB2) databases. You didn't specify which you're using.

This should work for SQL Server:


------------------------------
Tim Ferrill
Solutions Consultant
Intelligent Technology Solutions
tferrill@webuildits.com
www.webuildits.com
@tferrill/@webuildits
------------------------------

If you are using oracle database

select *
from workorder 
where istask = 0 and historyflag = 0 and targcompdate < trunc(sysdate,'month') ;

------------------------------
Omar Abou Ammar
Senior Technical Consultant
eSolutions FZ-LLC
Dubai
------------------------------

If you are using oracle and open work order are those where history flag is 0

select *
from  workorder
where istask = 0 and historyflag =0 and targcompdate < trunc(sysdate,'month')

If open workorders are those where not closed or canceled or completed (WAPPR,INPRG,APPR ,WSCH ... etc)

select *
from  workorder
where istask = 0 and targcompdate < trunc(sysdate,'month') and status not in (select value from synonymdomain where domainid = 'WOSTATUS' and maxvalue in ('CAN','CLOSE','COMP'))

------------------------------
Omar Abou Ammar
Senior Technical Consultant
eSolutions FZ-LLC
Dubai
------------------------------