Query help - partition effective dates and collapse rows
Date: Thu, 4 Feb 2010 11:30:19 -0600
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.
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
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