2013-09-15

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

No comments:

Post a Comment