RE: Need help with a query
- From: "Davey, Alan" <ddavey@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 29 Feb 2008 08:53:49 -0500
Hi,
Unfortunately that will only return two records instead of the three
records that I require.
Alan
________________________________
From: rulegen@xxxxxxxxx [mailto:rulegen@xxxxxxxxx] On Behalf Of Toon
Koppelaars
Sent: Friday, February 29, 2008 8:47 AM
To: Davey, Alan; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
--
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Toon Koppelaars
Managing partner
RuleGen BV
+31-615907269
toon@xxxxxxxxxxx
http://www.rulegen.com
Author: "Applied Mathematics for Database Professionals"
Other related posts:
