asked on

Oracle SQL Select Statement

I've got two tables in an ORA DB. One is called WORKORDER and the other is called WOSTATUS. They have a one to many relationship. For every row in the WORKORDER table, there is at least one row in the WOSTATUS table. The way the application works is that every time that you modify the status field of the work order, the application inserts a row into the WOSTATUS table. The tables have a number of columns but the ones the "connect" the two together are:

WONUM and SITEID.

The query that I'm attempting to write is one that finds WORKORDERs that have a status of 'INPRG' and where the previous status for those WORKORDERs, which you would find in the WOSTATUS table, is 'WMATL'.

I've attached an example to show you data in both tables and the result set I'm looking for.

I need to do this as a subselect with the subselect looking with the select looking for WORKORDERs with a status of 'INPRG' but then with the "subselect" limiting the result set to only those where the 2nd most recent WOSTATUS row is 'WMATL'.

I hope this make's sense.
Example-Tables-and-Result-Set.xlsx

PortletPaul

LAG() OVER() allows you to get the "previous" status value on each row, after that it is just matter of using filtering criteria

SELECT
      w.wonum
    , w.siteid
    , w.changedate
    , s.status
FROM (
      SELECT
            wonum
          , siteid
          , status
          , LAG(status, 1) OVER (PARTITION BY wonum, siteid ORDER BY statusdate DESC) lagstatus
      FROM wostatus
      ) s
INNER JOIN workorder w ON s.wonum = w.wonum
            AND s.siteid = w.siteid
WHERE s.lagstatus = 'WMATL'
      AND s.status = 'INPRG'
;

Open in new window

note it is the DESCending order of statusdate locates the "previous"

cbridgman

ASKER

This is close but it is not selecting only those WORKORDERs where status = 'INPRG'. Instead, it appears to return any work order that has 2 consecutive, related WOSTATUS records where the latter of the two is 'INPRG' and the one right before it is 'WMATL'

I've included an example on the spreadsheet - I've added Work Order 006 to illustrate. That one should not be in the result set but when I run the SQL that you provided it appears in the result set.

Thanks for your help, by the way

PortletPaul

I've included an example on the spreadsheet..... my spreadsheet isn't linked to yours :)

can you just post the sample data like this?

WOSTATUS
WONUM	SITEID	STATUS	STATUSDATE
001	ABC	WAPPR	2016-09-01
002	ABC	WAPPR	2016-08-01
002	ABC	APPR	2016-08-02
002	ABC	WMATL	2016-08-20
002	ABC	INPRG	2016-09-01
003	ABC	WAPPR	2016-08-10
003	ABC	APPR	2016-08-20
003	ABC	INPRG	2016-09-03
004	ABC	WAPPR	2016-08-22
004	ABC	APPR	2016-09-04
005	ABC	WAPPR	2016-08-01
005	ABC	APPR	2016-08-02
005	ABC	WMATL	2016-08-20
005	ABC	INPRG	2016-09-05

Open in new window

WORKORDER 
WONUM	SITEID	STATUS	CHANGEDATE
001	ABC	WAPPR	2016-09-01
002	ABC	INPRG	2016-09-02
003	ABC	INPRG	2016-09-03
004	ABC	APPR	2016-09-04
005	ABC	INPRG	2016-09-05

Open in new window

expected result:

WONUM	SITEID	CHANGEDATE
002	ABC	2016-09-02
005	ABC	2016-09-05

Open in new window

note also dates in yyyy-mm-dd make sense to the whole world (i'm not a citizen of the USA)

PortletPaul

try this:

SELECT
      w.wonum
    , w.siteid
    , w.changedate
    , w.status
FROM (
      SELECT
            wonum
          , siteid
          , status
          , LAG(status, 1) OVER (PARTITION BY wonum, siteid ORDER BY statusdate DESC) lagstatus
      FROM wostatus
      ) s
INNER JOIN workorder w ON s.wonum = w.wonum
            AND s.siteid = w.siteid
WHERE s.lagstatus = 'WMATL'
      AND s.status = 'INPRG'
      AND w.status = 'INPRG'
;

Open in new window

cbridgman

ASKER

Oops, forgot to attach the revised sheet. Here it is
Example-Tables-and-Result-Set.xlsx

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cbridgman

ASKER

Your solution almost worked but I had to remove the "AND s.status = 'INPRG' " from the where clause. When I did that, it ran like a charm. Still have to create some additional test workorders to make sure but the 4 that I already created work correctly

cbridgman

ASKER

may I ask 1 more question? Will the SQL that you provided me work with a SQL Server database? If not, can you tell me what I need to change?

awking00

LEAD/LAG were introduced in sql server 2012, so PortletPaul's code should work for that version. There are a number of workarounds for accomplishing the same thing for earlier versions. Just Google lag function in sql server 2008 (or whatever version) and you should find them.

cbridgman

ASKER

That helps a lot since the DB that I will need to use it on in the near future is SQL Server 2012

Thanks for the info.

I need to ask 1 last question because I want to be sure that points get distributed fairly.

As PortletPaul answered the bulk of the question for me, he should obviously get the bulk of the points. But you helped too with your answer so you deserve points. I have no idea what would be fair or better stated, I want to make sure that whatever I do choose is fair to both of you.

Any suggestions?

awking00

Please don't give me any points. Portlet Paul provided you with the solution and deserves the points. I just happened to know sql server introduced the lag function in sql server 2012. A simple web search would have easily discovered that. :-)

cbridgman

ASKER

thanks awking00, that makes things much easier. I appreciate your help.

cbridgman

ASKER

Thanks very much for your help on this.

cbridgman

ASKER

Maybe I should post another question for this but it is very closely related to the original.

Instead of looking at two tables in the query (WORKORDER and WOSTATUS), it dawned on me that I could really just look at the WOSTATUS table and get the same answer. In that query, Since one workorder can have many related rows in the row status table we would look in the WOSTATUS table for any workorder where the most recent status is INPRG. When we found that WOSTATUS record, we would then look at the 2nd most recent to find if the status of the record is WMATL. If it is, then we found a work order that meets our condition. If the most recent WOSTATUS record for a workorder is INPRG and the 2nd most recent record for that same work order is not WMATL, that one wouldn't be included in the result set.

Does that make sense. The attachments that I have provide can pretty much illustrate this.

Can you help with the SQL for this?

PortletPaul

Lol. I originally used the single table approach then realised you had asked for the date field from wostatus in the result
And that date is different in your sample data.

Then you added extra where clause conditions that required the wostatus table.

Are you certain you want just the one table?

PortletPaul

This is what a singe table version would look like

SELECT
      wonum
    , siteid
    , statusdate
    , status
FROM (
      SELECT
            wonum
          , siteid
          , statusdate
          , status
          , LAG(status, 1) OVER (PARTITION BY wonum, siteid ORDER BY statusdate DESC) lagstatus
      FROM wostatus
    ) 
WHERE lagstatus = 'WMATL'
      AND status = 'INPRG'
;

Open in new window

note it is the DESCending order of statusdate locates the "previous"

cbridgman

ASKER

hi again portlet paul

the single table query is close but not quite. I only want the result set to include a work order if it's most recent status is INPRG and the status just before was WMATL. In the following example, the result set should not include wonum 12345 because its most recent status record is COMP. It should, however, return wonum 45678 because its most recent status record is INPRG. When I run the SQL that you have just provided, it returns both

wonum status date
----------- --------- ----------------
12345 comp 2016-09-10
12345 inprg 2016-09-01
12345 wmatl 2016-08-07
12345 wappr 2016-08-05
45678 inprg 2016-09-01
45678 wmatl 2016-08-07
45678 wappr 2016-08-05

PortletPaul

Please try this and let me know.

SELECT
      wonum
    , siteid
    , statusdate
    , status
FROM (
      SELECT
            wonum
          , siteid
          , statusdate
          , status
          , LAG(status, 1) OVER (PARTITION BY wonum, siteid ORDER BY statusdate DESC) lagstatus
          , row_number() OVER (PARTITION BY wonum, siteid ORDER BY statusdate DESC) RN
      FROM wostatus
    ) 
WHERE lagstatus = 'WMATL'
      AND status = 'INPRG'
      AND RN = 1
;

Open in new window

cbridgman

ASKER

No records were returned that time and I made sure that one work order in the db met the criteria.

It's status changes, in order were

WAPPR
APPR
WMATL
INPRG

It started out as WAPPR and its most recent is INPRG

I think we are getting close though :-)