1

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.

  • I think if I was doing this I'd just approach it programatically rather than try and create a relational query to do it one go. Does that work for you or does it need to slot into something? – Paul Apr 10 '14 at 16:05
  • Currently an the SQL string sits in an Excel VBA and calls ADO or Access Database driver. It does the job for one month only. Select from... – Jacek Kotowski Apr 11 '14 at 10:58
  • @Paul I wonder if Transform/Pivot could somehow be used. On another sheet I wanted to present a calendar (when contracts end) where I used Transform / Pivot... ie.: strSQL = "TRANSFORM Count(Value) " & _ "SELECT [NIP] " & _ "FROM [Data$] " & _ "WHERE year(DateAdd('m',Dur_mths, DateSerial(year([Start_Date]),month([Start_Date]),1)))= " & Range("c2").Value & " " & _ "GROUP BY [NIP] " & _ "PIVOT Format(DateAdd('m',Dur_mths, DateSerial(year([Start_Date]),month([Start_Date]),1)),'yyyy-mm')" – Jacek Kotowski Apr 11 '14 at 11:05
  • For now I have a simple Select with a calculation for 1 month like this: ` Sum (iif(DateSerial(Year([Start_Date]),Month([Start_Date]),1) <= '2014-7-1' AND DateAdd ("m",[Dur_mths], DateSerial(Year([Start_Date]),Month([Start_Date]),1)) >= DateSerial(2014,6, 1) ,[Value]/[Dur_mths],0)) AS [M]` – Jacek Kotowski Apr 11 '14 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.