0

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 |
0

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.

0

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

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.