-1

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.

Raphael Yaghdjian
  • 79
  • 1
  • 8
  • Are you sure this is Oracle? There's no DATEADD nor DATEDIFF nor GETDATE there. – Littlefoot Oct 10 '18 at 05:09
  • this looks like SQL Server. You want the max change date only, or max change date for a specific status? Can you edit your question to clarify? – Sun Oct 10 '18 at 19:41
  • Hi, I will edit once I get back from work. So I want for all work orders to get the last appr status. Which would be either last wostatus.wostatusid or the last wostatus.changedate. – Raphael Yaghdjian Oct 10 '18 at 23:21

1 Answers1

1

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)
Vladislav Barsamov
  • 28
  • 2