Query help - partition effective dates and collapse rows
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- 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.
Thoughts?
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor@xxxxxxxxxxxxxxx<mailto:chris.taylor@xxxxxxxxxxxxxxx>
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.
Other related posts:
