My question is similar but more elaborate than another already in stackexchange.com "SQL query to return a column per date in date range?"
I have the following data:
Customer Start_date Duration_mths Value
Aaaaa 2014-01-01 4 200
Bbbbb 2014-03-04 12 120
Ccccc 2014-04-01 8 80
AAAAA 2014-01-03 4 100
Ccccc 2014-01-03 12 240
Ddddd 2013-12-05 3 60
What I would like to achieve is the sums of values divided by contract duration (evenly distributed contract utilisation) for each customer for each month if the contract falls betwen start date and end date (start plus dur. in months) :
Customer 2014-Jan 2014-Feb 2014-Mar 2014-Apr (my comments)
Aaaaa 50 50 50 75 (for Aaaa 2 contracts overlapped in April)
Bbbbb ... 0 0 10 10 (for Bbbb 1 contract started in march)
Ccccc ... 0 0 20 20
Ddddd... 60 0 0 0 (for Dddd 1 contract just ended)
I was looking for the solution in this post: Return a column per date in a range but it is only generating 1 if the booking is active and does not sum.
Thank you very much for all the help.
