Query help - partition effective dates and collapse rows

From: Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com>
Date: Thu, 4 Feb 2010 11:30:19 -0600
Message-ID: <C5533BD628A9524496D63801704AE56D379D0EA9E9_at_SPOBMEXC14.adprod.directory>

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.

Here's the data layout:

Boat ID, Service Type, Effective Dt
200 NEW 1/1/1900
200 CIN 11/12/2002

200 INS 1/1/2003
200 COU 6/1/2007
200 INS 9/9/2008
200 COU 10/3/2008

200 COU 11/1/2008
200 INS 11/23/2008
200 INS 1/31/2009
200 INS 2/3/2009
200 INS 4/5/2009

What I need to return is the following rows thus collapsing SERVICE_TYPES that are the same when ordered by EFFECTIVE_DT

200 NEW 1/1/1900 <- new partition
200 CIN 11/12/2002 <-new partition

200 INS 1/1/2003 <-new partition
200 COU 6/1/2007 < - new partition
200 INS 9/9/2008 <- new partition

200 COU 10/3/2008 <- Collapse two COU rows down to the min EFFECTIVE_DT (new partition) 200 INS 11/23/2008 <-- Collapse all INS rows down to the minimum EFFECTIVE_DT (new partition)

The above would be the final result set

Does it make any sense what I'm trying to do?

I think I'm going to have to have a subquery that returns the rows with the Effective Dates I want and then select from that subquery but not positive.

Obviously I'm trying to use analytic functions to come up with this.


Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor_at_ingrambarge.com<mailto:chris.taylor_at_ingrambarge.com>

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

Received on Thu Feb 04 2010 - 11:30:19 CST

Original text of this message