Hi guys, how can I allocate the right oil quantity for each month (see DDL)? I mean, OilQ 100 from 20-01-2015 to 20-02-2015 it should be 33,333 to January and 66,66 to February. SoI need a proportion between the date. Shall I create a calendar table or use a datediff?

create table #forum (Aplane int, OliQ int, byfrom date, byto date)
insert into #forum values ( 10, 100, '2012-01-01','2012-01-31'),
( 10, 200, '2012-02-01','2012-02-25'), ( 10, 80, '2012-02-26','2012-01-31'),
( 20, 200, '2012-01-01','2012-02-28')

It is not clear (at least to me) how you are doing your calculations.  Could you give us a more complete description of exactly what is going on?  Also the row with OliQ = 80 has byfrom as Feb 26, 2012 and byto as Jan 31, 2012.  Is that correct.  If it is, what does it mean when the from date is greater than the to date?

Tom

Hi DIEGOCTN,

Please see below sample based on my understanding on your requirement. A calendar table would be good to achieve your goal.

create table #forum (Aplane int, OliQ int, byfrom date, byto date)
insert into #forum values ( 10, 100, '2012-01-01','2012-01-31'),
( 10, 200, '2012-02-01','2012-02-25'), --( 10, 80, '2012-02-26','2012-01-31'),
( 20, 200, '2012-01-01','2012-02-28')

select * from #forum

;WITH Calendar AS
(
SELECT CAST('20120101' AS DATE) DT
UNION ALL
SELECT DATEADD(DAY,1,DT) FROM Calendar
WHERE DT <'20120228'
),
Cte AS(
SELECT Aplane,OliQ,byfrom,byto,CAST(DATEADD(MONTH,0,DATEDIFF(MONTH,0,DT)) AS DATE) [MONTH],COUNT(1) days FROM #forum f LEFT JOIN Calendar c
							ON c.DT BETWEEN f.byfrom AND f.byto 
							GROUP BY Aplane,OliQ,byfrom,byto,DATEADD(MONTH,0,DATEDIFF(MONTH,0,DT)) 
							)
SELECT c.*,cat.totaldays,CAST(c.days/(cat.totaldays*1.0) AS decimal(15,2))  Proportion  FROM Cte c
CROSS APPLY
(
SELECT SUM(days) totaldays FROM Cte WHERE Aplane=c.Aplane AND OliQ=c.OliQ AND byfrom = c.byfrom AND byto=c.byto
) cat
OPTION (MAXRECURSION 0)

If you have any question, feel free to let me know.