[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 126
  • Last Modified:

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
0
cbridgman
Asked:
cbridgman
  • 10
  • 7
  • 2
1 Solution
 
PortletPaulCommented:
sample data and expected resultLAG() 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"
0
 
cbridgmanAuthor Commented:
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
0
 
PortletPaulCommented:
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)
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
PortletPaulCommented:
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

0
 
cbridgmanAuthor Commented:
Oops, forgot to attach the revised sheet. Here it is
Example-Tables-and-Result-Set.xlsx
0
 
PortletPaulCommented:
sample data and expected resultall that is needed I believe is the extra where condition (last line)
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


nb: I'm not advocating for the use of images (which I have used to help subsequent readers if any)

I truly thank you for providing "sample data" and "expected result"
0
 
cbridgmanAuthor Commented:
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
0
 
cbridgmanAuthor Commented:
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?
0
 
awking00Commented:
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.
0
 
cbridgmanAuthor Commented:
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?
0
 
awking00Commented:
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. :-)
0
 
cbridgmanAuthor Commented:
thanks awking00, that makes things much easier. I appreciate your help.
0
 
cbridgmanAuthor Commented:
Thanks very much for your help on this.
0
 
cbridgmanAuthor Commented:
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?
0
 
PortletPaulCommented:
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?
0
 
PortletPaulCommented:
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"
0
 
cbridgmanAuthor Commented:
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
0
 
PortletPaulCommented:
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

0
 
cbridgmanAuthor Commented:
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 :-)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 10
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now