Re: SQL Query (Joining the table to itself)

From: <kestell_at_news.ipswich.gil.com.au>
Date: 1996/01/30
Message-ID: <kestell.3.0016CED6_at_news.ipswich.gil.com.au>#1/1

In article <4e8rtf$29d_at_thetimes.pixel.kodak.com> Pavan Muppidi <v919643_at_raven.cid.kodak.com> writes:
>From: Pavan Muppidi <v919643_at_raven.cid.kodak.com>
>Subject: SQL Query (Joining the table to itself)
>Date: 25 Jan 1996 21:15:27 GMT
 

>Hello,
 

>I have a frequency table as shown below.
 

>frequency Thru_Date Status
>------------------------------------------
> Monthly 01-JAN-96 C
> Monthly 01-FEB-96 I
> Monthly 01-MAR-96 I
> Monthly 01-APR-96 I
> Monthly 01-MAY-96 I
 

>I am trying to write a query to give the following result.
 

>Frequency From_Date To_Date
>----------------------------------------------
> Monthly 01-FEB-96 01-MAR-96
> Monthly 01-MAR-96 01-APR-96
> Monthly 01-APR-96 01-MAY-96
 

>I tried joining the table to itself using rownums, without much success.
>Please somebody help me with a idea how to accomplish this.
 

>Thanks in advance
 

>Pavan Muppidi

try this - I haven't tested it - assumed table name was thru_dates

SELECT finish.frequency,

              MAX(start.thru_date) From_Date, 
              finish.thru_date To_Date

FROM thru_dates start, thru_dates finish WHERE finish.status = 'I'

AND       start.start = finish.status
AND       finish.thru_date < start.thru_date

GROUP BY finish.frequency, finish.thru_date From_Date / Received on Tue Jan 30 1996 - 00:00:00 CET