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'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:
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
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
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(Q, 1, @NextPeriod_FirstDay)
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
No comments:
Post a Comment