## 2013-09-15

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

In the Part 1, I explained how to calculate "normal" periodic distribution result of occurred period, what if stakeholders want "financial" period types result. Let's take a look at the sample table:

 Periodic Type Amount Start Date Finish Date Total Days Period Proportion (Days) Expected Distribution Result Annual_FY 1000 2013-03-15 2013-07-10 118 FY2013 108 915.254237288136 FY2014 10 84.7457627118644 Quarterly_FY 1000 2013-03-15 2013-07-10 118 FY2013 Quarter3 17 144.067796610169 FY2013 Quarter4 91 771.186440677966 FY2014 Quarter1 10 84.7457627118644 Monthly_FY 1000 2013-03-15 2013-07-10 109 FY2013 M9 17 144.067796610169 FY2013 M10 30 254.237288135593 FY2013 M11 31 262.71186440678 FY2013 M12 30 254.237288135593 FY2014 M1 10 84.7457627118644 Weekly_FY 1000 2013-06-15 2013-07-10 26 FY2013 Week50 2 76.9230769230769 FY2013 Week51 7 269.230769230769 FY2013 Week52 7 269.230769230769 FY2014 Week1 7 269.230769230769 FY2014 Week2 3 115.384615384615

For Annual_FY, Quarterly_FY, and Monthly_FY type, they all have same query structure as normal period, the only difference is to identify the financial period of occurred date by occurred quarter. Please see the the following query and explanation of Annual_FY type section in the procedure:
IF @Periodic = 'Annual_FY'
BEGIN
SET @NextPeriod_FirstDay = DATEADD(YEAR, 1, @FY_StartDate)

IF    (@StartDate < @NextPeriod_FirstDay AND @FinishDate < @NextPeriod_FirstDay)
OR (@StartDate IS NOT NULL AND @FinishDate IS NULL)
BEGIN
INSERT INTO @tmp_Distribution
VALUES    (
CASE
WHEN DATEPART(Q, @StartDate) >= 3 THEN 'FY' + CONVERT(VARCHAR, YEAR(@StartDate)+1)
ELSE 'FY' + CONVERT(VARCHAR, YEAR(@StartDate))
END,
@Amount
)
END
ELSE
BEGIN
WHILE @StartDate <= @FinishDate
BEGIN
IF @StartDate = @FinishDate
BEGIN
INSERT INTO @tmp_Distribution
VALUES    (
CASE
WHEN DATEPART(Q, @StartDate) >= 3 THEN 'FY' + CONVERT(VARCHAR, YEAR(@StartDate)+1)
ELSE 'FY' + CONVERT(VARCHAR, YEAR(@StartDate))
END,
@Amount/1.0/@TotalDays * 1
)
END
ELSE
BEGIN
IF @NextPeriod_FirstDay <= @FinishDate
SET @Days = DATEDIFF(D, @StartDate, @NextPeriod_FirstDay)
ELSE
SET @Days = DATEDIFF(D, @StartDate, @FinishDate) + 1

SELECT @Days

INSERT INTO @tmp_Distribution
VALUES    (
CASE
WHEN DATEPART(Q, @StartDate) >= 3 THEN 'FY' + CONVERT(VARCHAR, YEAR(@StartDate)+1)
ELSE 'FY' + CONVERT(VARCHAR, YEAR(@StartDate))
END,
@Amount/1.0/@TotalDays * @Days
)
END

SET @StartDate = @NextPeriod_FirstDay
SET @NextPeriod_FirstDay = DATEADD(YEAR, 1, @NextPeriod_FirstDay)
END
END
END
For Quarterly_FY type, same structure as Annual_FY's, only need to change the definition of @NextPeriod_FirstDay, IF clause and insert value in the query:
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    (
CASE
WHEN DATEPART(Q, @StartDate) >= 3 THEN 'FY' +  CONVERT(VARCHAR, YEAR(@StartDate)+1) + ' Quarter' + CONVERT(VARCHAR, DATEPART(Q, @StartDate)-2)
ELSE 'FY' + CONVERT(VARCHAR, YEAR(@StartDate)) + ' Quarter' + CONVERT(VARCHAR, DATEPART(Q, @StartDate)+2)
END,
@Amount
)
END
ELSE

SET @NextPeriod_FirstDay = DATEADD(Q, 1, @NextPeriod_FirstDay)
For Monthly_FY type, same structure as Annual_FY'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    (
CASE
WHEN DATEPART(Q, @StartDate) >= 3 THEN 'FY' + CONVERT(VARCHAR, YEAR(@StartDate)+1) + ' M' + CONVERT(VARCHAR, MONTH(@StartDate)-6)
ELSE 'FY' + CONVERT(VARCHAR, YEAR(@StartDate)) + ' M' + CONVERT(VARCHAR, MONTH(@StartDate)+6)
END,
@Amount
)
END
ELSE
SET @NextPeriod_FirstDay = DATEADD(M, 1, @NextPeriod_FirstDay)
For Weekly_FY type, it is much more complicated than others, even more difficult than Weekly, especially need to identify the situations which cross different financial year and calculate weeks from the start date of financial year, please see the following query and explanations:
--Different logic from Weekly, add conditions to identify financial week and change start week of the year from @FY_StartDate
IF @Periodic = 'Weekly_FY'
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 financial year, if so, reset @NextPeriod_FirstDay to the first day of next financial year, become new start point
IF @NextPeriod_FirstDay >= DATEADD(YEAR, 1, @FY_StartDate)
SET @NextPeriod_FirstDay = DATEADD(YEAR, 1, @FY_StartDate)

SET @WeeksInLastYear = DATEPART(WEEK, DATEADD(D, -1, CONVERT(DATE, CONVERT(VARCHAR, YEAR(@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    (
CASE
WHEN DATEPART(Q, @StartDate) >= 3 THEN 'FY' + CONVERT(VARCHAR, YEAR(@StartDate)+1) + ' Week' + CONVERT(VARCHAR, DATEPART(WEEK, @StartDate) - DATEPART(WEEK, @FY_StartDate)+1)
ELSE 'FY' + CONVERT(VARCHAR, YEAR(@StartDate)) + ' Week' + CONVERT(VARCHAR, DATEPART(WEEK, @StartDate) + @WeeksInLastYear - DATEPART(WEEK, @FY_StartDate)-1)
END,
@Amount
)
END
ELSE
BEGIN
WHILE @StartDate <= @FinishDate
BEGIN
SET @WeeksInLastYear = DATEPART(WEEK, DATEADD(D, -1, CONVERT(DATE, CONVERT(VARCHAR, YEAR(@StartDate)))))

IF @StartDate = @FinishDate
BEGIN
INSERT INTO @tmp_Distribution
VALUES    (
CASE
WHEN DATEPART(Q, @StartDate) >= 3 THEN 'FY' + CONVERT(VARCHAR, YEAR(@StartDate)+1) + ' Week' + CONVERT(VARCHAR, DATEPART(WEEK, @StartDate) - DATEPART(WEEK, @FY_StartDate)+1)
ELSE 'FY' + CONVERT(VARCHAR, YEAR(@StartDate)) + ' Week' + CONVERT(VARCHAR, DATEPART(WEEK, @StartDate) + @WeeksInLastYear - DATEPART(WEEK, @FY_StartDate)-1)
END,
@Amount/1.0/@TotalDays * 1
)
END
ELSE
BEGIN
--SELECT @FY_StartDate, @NextPeriod_FirstDay, @StartDate, @FinishDate
--SELECT DATEPART(WEEK, @StartDate), @WeeksInLastYear, DATEPART(WEEK, @FY_StartDate)

IF @NextPeriod_FirstDay <= @FinishDate
SET @Days = DATEDIFF(D, @StartDate, @NextPeriod_FirstDay)
--Identify if the last week cross different financial year
ELSE IF @NextPeriod_FirstDay <= @FinishDate AND DATEPART(Q, @NextPeriod_FirstDay) = 3 AND DATEPART(Q, @StartDate) = 2
SET @Days = DATEDIFF(D, @StartDate, @FY_StartDate)
--Identify if the last week in the same financial year
ELSE
SET @Days = DATEDIFF(D, @StartDate, @FinishDate) + 1

SELECT @Days

INSERT INTO @tmp_Distribution
VALUES    (
CASE
WHEN DATEPART(Q, @StartDate) >= 3 THEN 'FY' + CONVERT(VARCHAR, YEAR(@StartDate)+1) + ' Week' + CONVERT(VARCHAR, DATEPART(WEEK, @StartDate) - DATEPART(WEEK, @FY_StartDate)+1)
ELSE 'FY' + CONVERT(VARCHAR, YEAR(@StartDate)) + ' Week' + CONVERT(VARCHAR, DATEPART(WEEK, @StartDate) + @WeeksInLastYear - DATEPART(WEEK, @FY_StartDate)-1)
END,
@Amount/1.0/@TotalDays * @Days
)
END

--Identify if the first day of next week cross next financial year, if so, reset @StartDate and @FY_StartDate to the first day of next financial year, become new start point, and also reset @NextPeriod_FirstDay
IF @NextPeriod_FirstDay >= DATEADD(YEAR, 1, @FY_StartDate)
BEGIN
SET @StartDate = DATEADD(YEAR, 1, @FY_StartDate)
SET @FY_StartDate = DATEADD(YEAR, 1, @FY_StartDate)
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