Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Unanswered: Dates betwen two dates and sum of a second field

    Hi all,

    I'm just getting into using sql server 2008 and the report builder 3.0 addon after a long time away from sql and I was wondering if the following was possible

    I have a number of records with a start and end date along with a volume i.e.

    Delivery Start; Delivery End; Volume
    01/06/12; 05/06/12; 25,000
    01/06/12; 02/06/12; 50,000
    24/05/12; 26/10/12; 5,000

    Essentially this means for each day between the two delivery dates the stated volume is delivered, what i need to try and do is to find the volume for each day between two user entered dates, giving a result along the lines of

    Start Date Entered = 01/06/12
    End Date Entered = 03/06/12

    Date; Total Volume
    01/06/12; 80,000
    02/06/12; 80,000
    03/06/12; 30,000

    Is this actually doable?

    Many Thanks
    Last edited by DaveC84; 06-25-12 at 09:36. Reason: Correction to expected output, thanks Tonkuma, my brain must have switched off briefly!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why not add this(24/05/12; 26/10/12; 5,000) volume?
    It is between 01/06/12 and 03/06/12.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It's definitely doable, and much simpler if you create a table of sequential values in your database. You can then join to the sequential values table to create a list of dates between any two dates.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jun 2012
    Posts
    2
    Thanks Blindman, After looking around it seems that is my best option to get it done

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A simple table of sequential numbers can simplify a lot of tricky datetime calculation problems.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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