# How to evenly distribute a value over varying time periods with T-SQL?

I am quite a newbie to MS SQL Server 2014 and in need for a sql skript that would retrieve evenly distributed budgets over given project durations.

The budget amounts need only to be equally distributed based on the total running months over the entire project duration.

Since durations widely vary between projects, end/start and entire years of project duration must be taken into account as well as monthly budget amounts need to be calculated at the same time, I can't come up with a concept of how to apply T-SQL to retrieve what I want.

Therefore, any ideas or sql snippets would be highly appreciated as well as a short explanation that a beginner could understand.

Data available within my project database:

``````|   id  |   budget  |   start    |      end        |
+-------+-----------+------------+-----------------+
|   1   |1000000,00 | 2001-06-01 |   2003-09-30    |
|   2   |3000000,00 | 2013-03-01 |   2016-06-30    |
``````

For the budget distribution, I want my final results to look like this:

``````|   id  |  Year_01  |  Year_02  |  Year_03   |  Year_04   |
+-------+-----------+-----------+------------+------------+
|   1   | 250000,00 | 428571,43 | 321428,57  |   NULL     |
|   2   | 750000,00 | 900000,00 | 900000,00  |  450000,00 |
``````

To solve this problem you'd need to figure out how many days are recieving a budget in each relevant year. I don't have the time to create a perfectly coded answer but this should definitely get you started.

``````DECLARE @Budget int = 1000000
DECLARE @Startdate date = '2001-06-01'
DECLARE @Enddate date = '2003-09-30'
DECLARE @nYears int = 1 --1 is required to make sure the current year is taken into account
DECLARE @dailyBudget int = DATEDIFF(D, @startdate,@endDate)
SET @nYears += (YEAR(@endDate)-YEAR(@Startdate))

DECLARE @cnt int = 0;
DECLARE @yearBudget int;

WHILE @cnt < @nYears
BEGIN
SET @yearBudget = @dailyBudget * (DATEDIFF(d, CAST(YEAR(@Startdate) + @cnt AS CHAR(4)) + '-01-01', CAST(YEAR(@Startdate) + @cnt AS CHAR(4)) + '-12-31') + 1 )

IF @cnt = 0 --If this is the starting year, calculate remaining days
SET @yearBudget = @dailyBudget * (DATEDIFF(d, @Startdate, CAST(YEAR(@Startdate) AS CHAR(4)) + '-12-31') +1)
IF @cnt = @nYears-1 --If this is the ending year, calculate elapsed days
SET @yearBudget = @dailyBudget * (DATEDIFF(d, --DailyBudget * days elapsed
CAST(YEAR(@Enddate) AS CHAR(4)) + '-01-01', --get the first day of the last year
@Enddate) +1) --Last day +1 so you get a budget for the last day as well

PRINT @yearBudget
SET @cnt += 1
END
``````

The key element of this answer is the DATEDIFF function. This is used to calculate the amount of days between a startdate and enddate. The current solution will print the budget for each year. Please note that I did NOT fully test this so you'd have to manually calculate if the results are correct.

This is the final version of the skript based on Stefans suggestion above combined with a cursor that will read through my data tables and inserts the result sets into a temporary table (##tmp):

``````IF OBJECT_ID ('tempdb.dbo.##tmp','U') IS NOT NULL
DROP TABLE ##tmp

CREATE TABLE ##tmp (P_ID int, budget decimal (10,2), YYYY varchar (8))

DECLARE c CURSOR LOCAL FOR
SELECT [...] FROM [...]

OPEN c

DECLARE @Budget decimal (10,2) = 0
DECLARE @id int = 0
DECLARE @Startdate date = NULL
DECLARE @Enddate date = NULL

FETCH NEXT FROM c INTO @id, @Budget, @Startdate, @Enddate

WHILE(@@FETCH_STATUS = 0)

BEGIN

DECLARE @nYears int = 0         --1 is required to make sure the current year is taken into account
DECLARE @dailyBudget decimal (10,2) = @Budget / (DATEDIFF(D, @Startdate,@Enddate) + 1)

SET @nYears = (YEAR(@Enddate)-YEAR(@Startdate))+1

DECLARE @cnt int = 0
DECLARE @yearBudget decimal (10,2);

WHILE @cnt < @nYears
BEGIN
SET @yearBudget = @dailyBudget * (DATEDIFF(d, CAST(YEAR(@Startdate) + @cnt AS CHAR(4)) + '-01-01', CAST(YEAR(@Startdate) + @cnt AS CHAR(4)) + '-12-31') + 1 ) --full years

IF @cnt = 0                                         -- If this is the starting year, calculate remaining days
SET @yearBudget = @dailyBudget * (DATEDIFF(d, @Startdate, CAST(YEAR(@Startdate) AS CHAR(4)) + '-12-31') + 1)

IF @cnt = @nYears - 1                               -- If this is the ending year, calculate elapsed days
SET @yearBudget = @dailyBudget * (DATEDIFF(d,   -- DailyBudget * days elapsed
CAST(YEAR(@Enddate) AS CHAR(4)) + '-01-01', -- get the first day of the last year
@Enddate) + 1)                          -- Last day +1 so you get a budget for the last day as well

IF @nYears = 1                                      -- If project period lies only within one year
SET @yearBudget = @Budget

INSERT INTO ##tmp VALUES (@id,@yearBudget, CONCAT('YYYY_', @cnt +1))

SET @cnt += 1
END

FETCH NEXT FROM c INTO @id, @Budget, @Startdate, @Enddate

END

CLOSE c
DEALLOCATE c
``````