Hi All I am trying to get the last wostatus.changedate in a query and i have the following code for Maximo (CMMS):
workorder.istask = 0 and (workorder.worktype = 'CM' or workorder.worktype = 'CP' or workorder.worktype = 'PM') and (workorder.woclass = 'WORKORDER' or workorder.woclass = 'ACTIVITY')
and (workorder.schedfinish >= dateadd(week, datediff(week, 0, getdate())+0, 0)
and workorder.schedfinish <= dateadd(week, datediff(week, 0, getdate())+1, 0)
and workorder.schedstart >= dateadd(week, datediff(week, 0, getdate())+0, 0)
and workorder.schedstart <= dateadd(week, datediff(week, 0, getdate())+1, 0))
and workorder.historyflag = 0 and workorder.siteid = 'CORE' and workorder.status != 'WPLAN' and workorder.wonum in
(select wostatus.wonum from wostatus where (wostatus.status = 'APPR') and wostatus.changeby in ('x', 'xx', 'xxx', 'xxxx' ) and wostatus.changedate >= dateadd(week, datediff(week, 7, getdate()), 2) and wostatus.changedate < dateadd(week, datediff(week, 0, getdate()), 3))
Below is the part of the code i need to filter the most current dates what I would need would be something like this, although I haven't been successful in bringing the most recent date:
(select wostatus.wonum from wostatus where (wostatus.status = 'APPR') and wostatus.changeby in ('x', 'xx', 'xxx', 'xxxx' ) and wostatus.changedate >= dateadd(week, datediff(week, 7, getdate()), 2) and wostatus.changedate = MOST RECENT DATE)
The whole query is looking at work orders in specific worktypes and scheduled during the current week, where they had their status last changed to APPR by specific people and this change has occurred from a specific time. I would like to see the last wostatus.status = 'APP' change by these people in wostatus.changeby.
At the moment my query only looks at the approved dates in a specific time and I want it to be the most recent approved date.
I tried to incorporate a Max(wostatus.changedate) in my query however I was unsuccessful.
Is this possible?
Thank you.
I am not sure what exactly you need, but try this for your last segment (plus all the change by and date filters)
select wonum from maximo.wostatus where status = 'APPR' and CHANGEDATE in
(select max(CHANGEDATE) from maximo.wostatus where status = 'APPR'
group by wonum)
This would give you the filter of most recently approved workorders. If you rather need the most recent by certain users - try this
select wonum from maximo.wostatus where status = 'APPR' and CHANGEDATE in
(select max(CHANGEDATE) from maximo.wostatus where status = 'APPR'
group by changeby)