RE: Need help with a query

From: Davey, Alan <ddavey_at_harris.com>
Date: Fri, 29 Feb 2008 08:53:49 -0500
Message-ID: <266D790CE98552478796D845439D354FDF50D9@mlbe2k6.cs.myharris.net>


Hi,  

Unfortunately that will only return two records instead of the three records that I require.  

Alan  


From: rulegen_at_gmail.com [mailto:rulegen_at_gmail.com] On Behalf Of Toon Koppelaars
Sent: Friday, February 29, 2008 8:47 AM
To: Davey, Alan; oracle-l_at_freelists.org
Subject: Re: Need help with a query  

Try:  

select min(start_air_date), max(end_air_date), num_units

from (<your original query without the order-by clause)

group by num_units

order by 1  

On 2/29/08, Davey, Alan <ddavey_at_harris.com> wrote:

Hi,

I have the following data:
>select min(start_air_date) start_date, max(end_air_date) end_date
, count(*) num_units
from br_bbdef_unit
where brdbuy_def_id = 352864
group by start_air_date, end_air_date
order by 1;

START_DAT END_DATE NUM_UNITS

--------- --------- ----------
07-JAN-08 11-JAN-08          1
14-JAN-08 18-JAN-08          1
21-JAN-08 25-JAN-08          1
28-JAN-08 01-FEB-08          1
04-FEB-08 08-FEB-08          3
11-FEB-08 15-FEB-08          1
18-FEB-08 22-FEB-08          1
25-FEB-08 29-FEB-08          1
03-MAR-08 07-MAR-08          1
10-MAR-08 14-MAR-08          1

What I would like to do is to group consecutive dates with the same number of units together to get their min/max values. So in this case I would end up with 3 records:

Start Date      End Date        Units/Wk
07-jan-08       01-feb-08       1
04-feb-08       08-feb-08       3
11-feb-08       14-mar-08       1

I'm thinking analytic functions may help here, but I haven't been able to figure out a query to produce the desired output.

Any help would be greatly appreciated.

Thanks,



Alan Davey
Senior Developer
Advertising Systems Group
Harris Corporation, Broadcast Communications 4 Century Drive
Parsippany, NJ 07054
--
http://www.freelists.org/webpage/oracle-l






-- 
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Toon Koppelaars
Managing partner
RuleGen BV
+31-615907269
toon_at_rulegen.com
http://www.rulegen.com

Author: "Applied Mathematics for Database Professionals" 


--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 29 2008 - 07:53:49 CST

Original text of this message