Re: Query help - partition effective dates and collapse rows
Date: Thu, 4 Feb 2010 13:06:25 -0500
Message-ID: <49d668001002041006s2ea45c37w4a71318081db6726_at_mail.gmail.com>
What you want to do is pretty straightforward using analytics
SQL> select * from boats;
BOAT_ID SER EFFECTIVE_DT
---------- --- ------------
200 NEW 01/01/1900 200 CIN 12/11/2002 200 INS 01/01/2003 200 COU 01/06/2007 200 INS 09/09/2008 200 COU 03/10/2008 200 COU 01/11/2008 200 INS 23/11/2008 200 INS 31/01/2009 200 INS 03/02/2009 200 INS 05/04/2009
11 rows selected
SQL>
SQL> select boat_id, service_type, min(effective_dt)
2 from (
3 select boat_id, service_type, effective_dt, sum(win) over
(partition by boat_id order by effective_dt) win_group
4 from (
5 select boat_id, service_type, effective_dt,
6 case when service_type = lag(service_type) over (partition by
boat_id order by effective_dt) then 0 else 1 end win
7 from boats
8 )) group by boat_id, service_type, win_group
9 order by 3;
BOAT_ID SER MIN(EFFECTIVE_DT)
---------- --- ----------------- 200 NEW 01/01/1900 200 CIN 12/11/2002 200 INS 01/01/2003 200 COU 01/06/2007 200 INS 09/09/2008 200 COU 03/10/2008 200 INS 23/11/2008
7 rows selected
On Thu, Feb 4, 2010 at 12:30 PM, Taylor, Chris David
<ChrisDavid.Taylor_at_ingrambarge.com> wrote:
> Ok Guys/gals, I need a pointer in the right direction as I cannot seem to
> get this query to return the rows I want.
>
-- Alex Fatkulin, http://afatkulin.blogspot.com http://www.linkedin.com/in/alexfatkulin -- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 04 2010 - 12:06:25 CST