Need help with a query
From: Davey, Alan <ddavey_at_harris.com>
Date: Fri, 29 Feb 2008 08:36:35 -0500
Message-ID: <266D790CE98552478796D845439D354FDF50BC@mlbe2k6.cs.myharris.net>
Alan Davey
Senior Developer
Advertising Systems Group
Harris Corporation, Broadcast Communications 4 Century Drive
Parsippany, NJ 07054
Date: Fri, 29 Feb 2008 08:36:35 -0500
Message-ID: <266D790CE98552478796D845439D354FDF50BC@mlbe2k6.cs.myharris.net>
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-lReceived on Fri Feb 29 2008 - 07:36:35 CST