SQL Server - Calculate input amount by using periodic proportion of occurred period - Part 1

Sometimes stakeholders want to know periodic(annual, quarterly, monthly, weekly, or daily, etc.) distribution result from amount of cost or working hours during occurred period. It is very easy to calculate periodic result if dataset only uses 1 timestamp field(e.g. TransDate) to identify when the record happened by using GROUP BY clause. When dataset uses 2 timestamp fields(e.g. StartDate and FinishDate) to identify occurred period of the record, for retrieving periodic distribution result, the most important part of the logic is to identify proportion of each type of periodic, please see the following examples:

Periodic Type	Amount	Start Date	Finish Date	Total Days	Period	Proportion (Days)	Expected Distribution Result
Annual	1000	2013-09-15	2014-01-01	109	2013	108	990.825688073395
					2014	1	9.1743119266055
Quarterly	1000	2013-09-15	2014-01-01	109	2013 Quarter3	16	146.788990825688
					2013 Quarter4	92	844.036697247706
					2014 Quarter1	1	9.1743119266055
Monthly	1000	2013-09-15	2014-01-01	109	2013 September	16	146.788990825688
					2013 October	31	284.403669724771
					2013 November	30	275.229357798165
					2013 December	31	284.403669724771
					2014 January	1	9.1743119266055
Weekly	1000	2013-12-15	2014-01-01	18	2013 Week50	1	55.5555555555556
					2013 Week51	7	388.888888888889
					2013 Week52	7	388.888888888889
					2013 Week53	2	111.111111111111
					2014 Week1	1	55.5555555555556
Daily	1000	2013-12-28	2014-01-01	5	2013-12-28	1	200
					2013-12-29	1	200
					2013-12-30	1	200
					2013-12-31	1	200
					2014-01-01	1	200

As you can see from the table above, the result of period 2013 in Annual type, its calculation: 108/109 * 1000 = 990.825688073395

The second important part of the logic is to identify what is the first day of next period such as the first of next year for Annual, or the first day of next quarter for Quarterly, etc. Base on the consideration of reusing the query in data analyzing, initial idea is to create a function rather than copy bunch of queries in other stored procedure or function, but SQL Server doesn't allow me to use SET statements in the function, so change to create a stored procedure which includes all periodic types, please see the the following query and explanation of Annual type section in the procedure:

CREATE PROCEDURE [dbo].[usp_Distribution_By_Periodic_Proportion]
(
    @Periodic       VARCHAR(50),
    @Amount        FLOAT,
    @StartDate      DATE,
    @FinishDate    DATE
)
AS
    --For debugging
    /*
    DECLARE    @Periodic    VARCHAR(50),
            @Amount        FLOAT,
            @StartDate      DATE,
            @FinishDate    DATE
   
    SET @Periodic = 'Annual'--'Annual', 'Annual_FY', 'Quarterly', 'Quarterly_FY', 'Monthly', 'Monthly_FY', 'Weekly', 'Weekly_FY', 'Daily'
    SET @Amount = 1000
    SET @StartDate = '2013-12-15'
    SET @FinishDate = '2014-01-01'
    */

    DECLARE @tmp_Distribution TABLE
        (Period VARCHAR(50), Amount FLOAT)
  
    DECLARE @TotalDays          INT,
            @FY_StartDate                DATE,
            @NextPeriod_FirstDay    DATE,
            @Days                              INT          

    SET @FY_StartDate = CASE
                            WHEN DATEPART(Q, @StartDate) >= 3 THEN CONVERT(DATE, CONVERT(VARCHAR, YEAR(@StartDate)) + '-07-01')
                            ELSE CONVERT(DATE, CONVERT(VARCHAR, YEAR(@StartDate)-1) + '-07-01')
                        END
    SET @TotalDays = DATEDIFF(D, @StartDate, @FinishDate) + 1
  
    --SELECT @FY_StartDate
    --SELECT @TotalDays  
      
    IF @Periodic = 'Annual'
    BEGIN
        SET @NextPeriod_FirstDay = CONVERT(DATE, CONVERT(VARCHAR, YEAR(@StartDate)+1))

        --Identify if @StartDate and @FinishDate are in the same year.
        --If @FinishDate is null, it means the projects/tasks/assignments not yet finished, in case of returning nothing, temporarily define @FinishDate is same year as @StartDate.  
        IF    (YEAR(@StartDate) = YEAR(@FinishDate))
            OR (@StartDate IS NOT NULL AND @FinishDate IS NULL)
            BEGIN
                INSERT INTO @tmp_Distribution
                VALUES    (  
                            CONVERT(VARCHAR, YEAR(@StartDate)),
                            @Amount
                        )
            END
        ELSE
            BEGIN
                WHILE @StartDate <= @FinishDate
                BEGIN
                    --Identify if @FinishDate is the first day of next period
                    IF @StartDate = @FinishDate
                    BEGIN
                        INSERT INTO @tmp_Distribution
                        VALUES    (
                                    CONVERT(VARCHAR, YEAR(@StartDate)),
                                    @Amount/1.0/@TotalDays * 1
                                )
                    END
                    ELSE
                    BEGIN
                        --Retrieve days of the each loop/period, or second last loop which its @NextPeriod_FirstDay is qual to @FinishDate
                        IF @NextPeriod_FirstDay <= @FinishDate
                            SET @Days = DATEDIFF(D, @StartDate, @NextPeriod_FirstDay)
                        --Retrieve days of the last loop for the last/remain period
                        ELSE
                            SET @Days = DATEDIFF(D, @StartDate, @FinishDate) + 1
                      
                        --SELECT @Days
                                      
                        INSERT INTO @tmp_Distribution
                        VALUES    (
                                    CONVERT(VARCHAR, YEAR(@StartDate)),
                                    @Amount/1.0/@TotalDays * @Days
                                )
                    END      
                      
                    SET @StartDate = @NextPeriod_FirstDay                                             
                    SET @NextPeriod_FirstDay = DATEADD(YEAR, 1, @NextPeriod_FirstDay)
                END
            END
    END

For Quarterly type, it has similar structure to Annual's, only need to change the definition of @NextPeriod_FirstDay, IF statements and insert value in the query, the rest parts would be the same as Annual's:

SET @NextPeriod_FirstDay = CONVERT(DATE, CONVERT(VARCHAR, YEAR(@StartDate)) + '-' + CONVERT(VARCHAR, DATEPART(Q, @StartDate)*3+1) + '-01')

IF    (YEAR(@StartDate) = YEAR(@FinishDate) AND DATEPART(Q, @StartDate) = DATEPART(Q, @FinishDate))
            OR (@StartDate IS NOT NULL AND @FinishDate IS NULL)
            BEGIN
                INSERT INTO @tmp_Distribution
                VALUES    (   
                            CONVERT(VARCHAR, YEAR(@StartDate)) + ' Quarter' + DATENAME(Q, @StartDate),
                            @Amount
                        )
            END
        ELSE

SET @NextPeriod_FirstDay = DATEADD(Q, 1, @NextPeriod_FirstDay)

For Monthly type, same structure as Annual's, only need to change the definition of @NextPeriod_FirstDay, IF clause and insert value in the query:

SET @NextPeriod_FirstDay = DATEADD(M, 1, DATEADD(D, -DAY(@StartDate)+1, @StartDate))

IF    (YEAR(@StartDate) = YEAR(@FinishDate) AND MONTH(@StartDate) = MONTH(@FinishDate))
            OR (@StartDate IS NOT NULL AND @FinishDate IS NULL)
            BEGIN
                INSERT INTO @tmp_Distribution
                VALUES    (   
                            CONVERT(VARCHAR, YEAR(@StartDate)) + ' ' + DATENAME(M, @StartDate),
                            @Amount
                        )
            END
        ELSE

SET @NextPeriod_FirstDay = DATEADD(M, 1, @NextPeriod_FirstDay)

For Daily type, it even simpler than the types above, follow the same logic, so I ignore this type here. For Weekly type, it is more complicated than others, especially need to identify the situations which cross different year, please see the following query and explanations:

IF @Periodic = 'Weekly'
    BEGIN
        --Set first day of the week is Monday, system default is Sunday.
        SET DATEFIRST 1
      
        SET @NextPeriod_FirstDay = DATEADD(D, 7-DATEPART(W, @StartDate)+1, @StartDate)
      
        --Identify if the first day of next week cross next year, if so, reset @NextPeriod_FirstDay to the first day of next year, become new start point
        IF @NextPeriod_FirstDay >= CONVERT(DATE, CONVERT(VARCHAR, YEAR(@StartDate)+1))
            SET @NextPeriod_FirstDay = CONVERT(DATE, CONVERT(VARCHAR, YEAR(@StartDate)+1))
      
        --Identify if @StartDate and @FinishDate are in the same week.
        --If @FinishDate is null, it means the projects/tasks/assignments not yet finished, in case of returning nothing, temporarily define @FinishDate is same week as @StartDate.
        IF    (YEAR(@StartDate) = YEAR(@FinishDate) AND DATEPART(WEEK, @StartDate) = DATEPART(WEEK, @FinishDate))
            OR (@StartDate IS NOT NULL AND @FinishDate IS NULL)
            BEGIN
                INSERT INTO @tmp_Distribution
                VALUES    (  
                            CONVERT(VARCHAR, YEAR(@StartDate)) + ' Week' + DATENAME(WEEK, @StartDate),
                            @Amount
                        )
            END
        ELSE
            BEGIN
                WHILE @StartDate <= @FinishDate
                BEGIN                  
                    IF @StartDate = @FinishDate
                    BEGIN
                        INSERT INTO @tmp_Distribution
                        VALUES    (
                                    CONVERT(VARCHAR, YEAR(@StartDate)) + ' Week' + DATENAME(WEEK, @StartDate),
                                    @Amount/1.0/@TotalDays * 1
                                )
                    END
                    ELSE
                    BEGIN
                        --SELECT @NextPeriod_FirstDay, @StartDate, @FinishDate
                      
                        IF @NextPeriod_FirstDay <= @FinishDate
                            SET @Days = DATEDIFF(D, @StartDate, @NextPeriod_FirstDay)
                        --Identify if the last week cross different year
                        ELSE IF @NextPeriod_FirstDay > @FinishDate AND YEAR(@NextPeriod_FirstDay) <> YEAR(@StartDate)
                            SET @Days = DATEDIFF(D, @StartDate, CONVERT(DATE, CONVERT(VARCHAR, YEAR(@StartDate)+1)))
                        --Identify if the last week in the same year
                        ELSE
                            SET @Days = DATEDIFF(D, @StartDate, @FinishDate) + 1
                      
                        --SELECT @Days
                                      
                        INSERT INTO @tmp_Distribution
                        VALUES    (
                                    CONVERT(VARCHAR, YEAR(@StartDate)) + ' Week' + DATENAME(WEEK, @StartDate),
                                    @Amount/1.0/@TotalDays * @Days
                                )
                    END      
                  
                    --Identify if the first day of next week cross next year, if so, reset @StartDate to the first day of next year, become new start point, and also reset @NextPeriod_FirstDay.
                    IF @NextPeriod_FirstDay >= CONVERT(DATE, CONVERT(VARCHAR, YEAR(@StartDate)+1))
                    BEGIN
                        SET @StartDate = CONVERT(DATE, CONVERT(VARCHAR, YEAR(@StartDate)+1))
                        SET @NextPeriod_FirstDay = DATEADD(D, 7-DATEPART(W, @StartDate)+1, @StartDate)
                    END
                    ELSE
                    BEGIN  
                        SET @StartDate = @NextPeriod_FirstDay
                        SET @NextPeriod_FirstDay = DATEADD(WEEK, 1, @NextPeriod_FirstDay)
                    END
                END
            END
    END