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

    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:

    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:

    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.

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

  2. #2
    Join Date
    Jun 2003
    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 "sqlblindman"

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

    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
    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