T-SQL – How to Evenly Distribute a Value Over Varying Time Periods

sql server 2014t-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 |

Best Answer

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.

Related Question