Home » SQL & PL/SQL » SQL & PL/SQL » Query Problem  () 1 Vote
icon8.gif  Query Problem [message #243166] Wed, 06 June 2007 05:36 Go to next message
barakula
Messages: 5
Registered: June 2007
Junior Member
Hi all

I need your help getting results from a table :

1555064	13/05/2007 08:15:40	16/05/2007 21:02:36
1555064	20/05/2007 09:40:30	24/05/2007 18:22:47
1555064	24/05/2007 18:25:38	28/05/2007 16:50:00
1555064	28/05/2007 17:16:25	


as you can see I have 3 columns :

account,open_date, close_date

I am trying to write a query that will show me only the open_date and the close_date
which are not the same (let me rephrase that) :

If the close_date and the open_date afterwards are not on the same day ,
This is what ia m looking for .

in this example

I am expecting to see this :

1555064 16/05/2007 21:02:36 20/05/2007 09:40:30

because the account was closed on the 16th and reopened 4 days after on the 20th !

Please help ....

P.s
The table is filled with a lot more accounts of course .
Many of them got opened and closed on the same day and are not interesting for me .

Barak
Re: Query Problem [message #243171 is a reply to message #243166] Wed, 06 June 2007 05:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If I understand it correctly, you want the following:
the records from yourtable that have, ordered by open_date, a gap between the day of closure and the next opening day.

Analytic functions can do the job. Have a look at the Oracle manuals for LAG() and LEAD().

Here's a basic query:
SELECT account
     , open_date
     , close_date
FROM   ( SELECT account
              , TRUNC(open_date)  open_date
              , TRUNC(close_date) close_date
              , LEAD(TRUNC(open_date)) OVER ( PARTITION BY account ORDER BY close_date) next_open              
         FROM   yourtable
         ORDER  BY open_date
       )
WHERE  close_date != next_open
/


MHE
icon7.gif  Re: Query Problem [message #243185 is a reply to message #243166] Wed, 06 June 2007 06:55 Go to previous messageGo to next message
barakula
Messages: 5
Registered: June 2007
Junior Member
Thank you very much Laughing

That helped a lot !

I am now reading about those functions

Barak .
Re: Query Problem [message #243187 is a reply to message #243185] Wed, 06 June 2007 07:03 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Just in curiosity…there is any other way to do this except to this

--Yash
Re: Query Problem [message #243199 is a reply to message #243187] Wed, 06 June 2007 07:57 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Yep, you have other ways, but none as elegant as this. One other way of doing this is to sort the data in an inner view, add rownum as a sort of ranking and compare consecutive records of the same account. This would imply that you have to use the same query twice because you cannot compare different rows without analytic functions. I guess it would be something like:
SELECT v1.account
     , v1.close_date
     , v2.open_date
     
FROM   ( -- ADD ROWNUM --> simulate a ranking
         SELECT account
              , open_date
              , close_date
              , rownum rn
         FROM ( -- SORT THE DATA
                SELECT account
                     , TRUNC(open_date)  open_date
                     , TRUNC(close_date) close_date
                FROM   yourtable
                ORDER  BY open_date
              ) 
       ) v1
   ,   ( -- ADD ROWNUM --> simulate a ranking
         SELECT account
              , open_date
              , close_date
              , rownum rn
         FROM ( -- SORT THE DATA
                SELECT account
                     , TRUNC(open_date)  open_date
                     , TRUNC(close_date) close_date
                FROM   yourtable
                ORDER  BY open_date
              ) 
       ) v2
WHERE  v1.account = v2.account
AND    v1.rn = v2.rn - 1
AND    v2.open_date != v1.close_date
/

My guess is that the analytic function outperforms this one.

MHE
Previous Topic: Function using execute immediate
Next Topic: Diff Between 'Exist IN' and 'IN'
Goto Forum:
  


Current Time: Wed Dec 07 22:18:17 CST 2016

Total time taken to generate the page: 0.32282 seconds