Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    31

    Unanswered: Consolidating dates from multiple rows

    I am having a bit of a problem over here. I am trying to consolidate dates from multiple records into a time line that has no date overlap. I'll give you an example to make things clear.

    Let's say I have 3 data records:

    Code:
    RowID BeginDate EndDate Price ($)
    ----------------------------------------------------
    1 01/01/2008 01/10/2008 1.00
    2 01/05/2008 01/15/2008 2.00
    3 12/20/2007 02/01/2008 1.50
    The result I would like to see should look like this:

    Code:
     
    12/20/2007 - 12/31/2007 the price was 1.50
    01/01/2008 - 01/10/2008 the price was 2.50 because row 1 and 3 overlap.
    01/05/2008 - 01/15/2008 the price was 3.50 because row 2 and 3 overlap.
    01/16/2008 - 02/01/2008 the price was 1.50 because of the row 3.
    Any idea on how I can automate generation of this data?

    I have a lot of code written for that but I can't get the result I want.
    I don't know if someone wants to see my code, I got around 500 lines of it.

    I would appreciate any help with this.

    Thanks!
    Last edited by mumick; 02-19-08 at 18:14.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So the price is the SUM of the prices applicable on a given day?

    Do you have a table of sequential numbers in your database? You are going to need one.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2005
    Posts
    31
    No, I don't have it in the real table but I am using table variable with the RowID column set as an identity.

    Code:
    declare @Prices table
    (
         RowID int identity(1,1) not null,
         BeginDate datetime not null,
         EndDate datetime not null,
         Price numeric(19,5) not null
    )

  4. #4
    Join Date
    Jan 2005
    Posts
    31
    Yes, the price is the sum of the prices for the date ranges that overlap.
    Last edited by mumick; 02-19-08 at 22:43.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •