5 Replies Latest reply: Nov 23, 2010 2:14 PM by Maria.Delaney RSS

    Calculate daily quantity from date range

    Maria.Delaney

      Hi. I'm new to the community so go easy on me [:)]

      I'm trying to calculate daily quantities based on a range of dates (start date and end date). I'll try and explain a little better through a simple example:

       

      StartDate

      EndDate

      Qty

      01/10/10

      05/10/10

      10

      02/10/10

      03/10/10

      6



      The 'Qty' should be spread across all the days in the range meaning 2 (10/2) is counted for 5 days for the first record and 3 is counted for 2 days from the second record. This boils down to the two records above producing the following output (to chart)

       

      Date

      Qty for that day

      01/10/10

      2

      02/10/10

      5

      03/10/10

      5

      04/10/10

      2

      05/10/10

      2



      (I hope I've worked this out right!)

      I'm resigned to the fact the answer will probably involve aggr functions of some kind but thinking about them too much just makes me aggry!

      Thanks Guys

      Maria