Home » SQL & PL/SQL » SQL & PL/SQL » Query Help!
Query Help! [message #223569] Fri, 09 March 2007 10:16 Go to next message
lowcash75
Messages: 67
Registered: February 2006
Member
I have a table with the following data:

id status date
--- ------ ----
1 APPROVED 10-May-2000 2:27:29
2 APPROVED 11-May-2000 3:27:29
2 DISAPPROVED 12-May-2000 3:27:29
2 APPROVED 13-May-2000 3:27:29
3 APPROVED 14-May-2000 3:27:29
3 DISAPPROVED 15-May-2000 3:27:29


I want to be able to only get records that have been "APPROVED", if there is an "APPROVED" record for an ID after a "DISAPPROVED" then I need that also. If the record was "DISAPPROVED" after an "APPROVED" then I can disregard that.

Final Result:

id status date
--- ------ ----
1 APPROVED 10-May-2000 2:27:29
2 APPROVED 13-May-2000 3:27:29

I can do it in a SP, but rather do it in a query if possible.

Thanks!
Re: Query Help! [message #223598 is a reply to message #223569] Fri, 09 March 2007 13:32 Go to previous messageGo to next message
atagore
Messages: 29
Registered: March 2006
Junior Member
You can do a UNION of 3 queries
(1) Approve with no Disapprove
(2) Approve after Disapprove
(3) Disapprove is latest date
Re: Query Help! [message #223647 is a reply to message #223598] Sat, 10 March 2007 00:34 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
or you can use analytical functions like this


SCOTT@test>select * from dummy;

        ID STATUS               DT
---------- -------------------- -----------
         1 APPROVED             10-May-2000
         2 APPROVED             11-May-2000
         2 DISAPPROVED          12-May-2000
         2 APPROVED             13-May-2000
         3 APPROVED             14-May-2000
         3 DISAPPROVED          15-May-2000

6 rows selected.

SCOTT@test>select id,status,dt,
  2  max(dt) over (partition by id) maxdt
  3  from dummy
  4  /

        ID STATUS               DT          MAXDT
---------- -------------------- ----------- -----------
         1 APPROVED             10-May-2000 10-May-2000
         2 APPROVED             11-May-2000 13-May-2000
         2 DISAPPROVED          12-May-2000 13-May-2000
         2 APPROVED             13-May-2000 13-May-2000
         3 APPROVED             14-May-2000 15-May-2000
         3 DISAPPROVED          15-May-2000 15-May-2000

6 rows selected.


SCOTT@test> select * from (
  2   select id,status,dt,
  3   max(dt) over (partition by id) maxdt
  4   from dummy)
  5   where dt=maxdt and status = 'APPROVED'
  6  /

        ID STATUS               DT          MAXDT
---------- -------------------- ----------- -----------
         1 APPROVED             10-May-2000 10-May-2000
         2 APPROVED             13-May-2000 13-May-2000

2 rows selected.


SCOTT@test> drop table dummy;

Table dropped

SCOTT@test>



HTH

Re: Query Help! [message #223744 is a reply to message #223647] Sat, 10 March 2007 21:02 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What is the love-affair with sorts? Has the world forgotten how to do sub-queries?

SELECT *
FROM tbl a
WHERE status = 'APPROVED'
AND NOT EXISTS (
  SELECT 1
  FROM   tbl
  WHERE  id = a.id
  AND    status = 'DISAPPROVED'
  AND    dt > a.dt)



Ross Leishman
Previous Topic: run a script
Next Topic: multiple rows
Goto Forum:
  


Current Time: Mon Dec 05 05:02:29 CST 2016

Total time taken to generate the page: 0.07509 seconds