Avatar

Please consider registering
guest

sp_LogInOut Log In sp_Registration Register

Register | Lost password?
Advanced Search

— Forum Scope —




— Match —





— Forum Options —





Minimum search word length is 3 characters - maximum search word length is 84 characters

sp_Feed Topic RSS sp_TopicIcon
Date Based Status Snapshot
SQL help, historical snap shot regardless of current status
October 13, 2016
4:07 pm
Avatar
LHOU16
New Member
Members
Forum Posts: 1
Member Since:
October 13, 2016
sp_UserOfflineSmall Offline

Hello,

I am a Maximo super user and not an IT professional, I build simple queries and piece together more complex one from previousily used queries. I am trying to get a snapshot of data from a month based on active status’ and I can’t seem to get the results I am looking for. For example below I am tring to gather all the work orders with:
1. Classification = Vessel (ancestor = ‘1557’)
2. Work Order Status not in (‘INCOMP’,’WORKCOMP’,’COMP’,’CAN’,’CLOSE’) completed status
3. Date = the month of March 2016 (All work orders not in these status as of March 1-31 or even date <= March 31st, regardless of the current status) 

where(status not in (select value from synonymdomain where domainid = ‘WOSTATUS’ and maxvalue in (‘INCOMP’,’WORKCOMP’,’COMP’,’CAN’,’CLOSE’)))and((woclass = ‘WORKORDER’ or woclass = ‘ACTIVITY’) and historyflag = 0 and istask = 0 and siteid = ‘PWCAN’)and assetnum in (select assetnum from asset where (exists (select 1 from maximo.classancestor where ((ancestor = ‘1557’))
and(classstructureid=asset.classstructureid))))and maintacttype = ‘INSPECTION’ and (extract(year from statusdate)=extract(year from sysdate) and extract(month from statusdate)=extract(month from date ‘1998-03-01’))and location in (select location from locancestor where ancestor in (select location from locationusercust where personid=   :&personid&  ))

Thanks in advance for any super basic insight someone might have.

October 14, 2016
8:29 am
Avatar
Chon
Stoneham, MA USA
Admin
Forum Posts: 1118
Member Since:
March 2, 2009
sp_UserOfflineSmall Offline

Hello, Please register to see the Admin’s answer!

Forum Timezone: America/New_York

Most Users Ever Online: 54

Currently Online:
1 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

sun kim: 60

madd0g17: 59

v.barsamov: 51

cgregory: 45

Maximofresher: 45

matta0990: 33

Member Stats:

Guest Posters: 0

Members: 14366

Moderators: 0

Admins: 4

Forum Stats:

Groups: 1

Forums: 8

Topics: 1580

Posts: 4295

Newest Members:

Megan Morgan, Devendra Verma, stephen stephens, maxexpertise Hilal, Jeroen van Proosdij, Neeraja N

Administrators: Chon: 1118, kristensibilia: 0, Joe Grassia: 0, bpaik: 0