ALTER TABLE days
ADD
fiscal_year smallint,
fiscal_month smallint,
fiscal_week_in_month tinyint,
fiscal_week_in_year tinyint,
fiscal_day_in_month tinyint,
fiscal_day_in_year tinyint
Let's populate them with an easy example: A fiscal year that runs October 1 to September 30. October is considered month #1, November is month #2, to September is month #12. Each month has the same days as the calendar month, 1 through 28-31.
-- The fiscal year is the same calendar year as the last nine months
UPDATE days
SET fiscal_year =
CASE WHEN calendar_month IN (10, 11, 12) THEN calendar_year + 1
ELSE calendar_year END
UPDATE days
SET fiscal_month = CASE calendar_month
WHEN 10 THEN 1 WHEN 11 THEN 2 WHEN 12 THEN 3
WHEN 1 THEN 4 WHEN 2 THEN 5 WHEN 3 THEN 6
WHEN 4 THEN 7 WHEN 5 THEN 8 WHEN 6 THEN 9
WHEN 7 THEN 10 WHEN 8 THEN 11 WHEN 9 THEN 12 END
Now you can query these columns...
USE calendar
GO
/*
Calendar table: Populate the six fiscal_ columns
2015-09-16 jim@jimhorn.biz
*/
Declare @dtYearStart date, @dtStart date, @dtEnd date, @dt date
Declare @fiscal_month tinyint = 1, @fiscal_year smallint , @fiscal_week_in_month tinyint, @fiscal_week_in_year tinyint, @fiscal_day_in_week tinyint, @fiscal_day_in_month tinyint
Declare @counter int = 1, @counter_year int = 1, @counter_month int = 1, @counter_week int = 1, @counter_day int = 1
-- Run this for 19 years from May 2000 to May 2020
WHILE @counter_year <= 19
begin
-- Per the article image, the last day of the year is the last Sunday in May.
SELECT @dtYearStart = MAX(PKDate), @dtEnd = MAX(PKDate)
FROM days
WHERE continuous_year = @counter_year AND calendar_month = 5 AND calendar_day_in_week = 1
-- YEARS and MONTHS
-- Set the year
SELECT @fiscal_year = YEAR(@dtYearStart) + 1, @fiscal_month = 1
SET @counter = 1
WHILE @counter <= 12
begin
SELECT @dtStart = DATEADD(day, 1, @dtEnd)
SELECT @dtEnd = DATEADD(day, CASE WHEN @fiscal_month IN (1, 4, 7, 10) THEN 34 ELSE 27 END, @dtStart)
UPDATE days
SET fiscal_year = @fiscal_year, fiscal_month = @fiscal_month
FROM days
WHERE PKDate >= @dtStart AND PKDate <= @dtEnd
;WITH ro AS (SELECT PKDate, RANK() OVER (ORDER BY PKDate) as row_order FROM days WHERE fiscal_year = @fiscal_year AND fiscal_month = @fiscal_month)
UPDATE days
SET fiscal_day_in_month = row_order
FROM days
JOIN ro ON days.PKDate = ro.PKDate
-- TESTING ONLY, comment the below line out in production
-- SELECT 'Year and Month' as label, PKDate, fiscal_year, fiscal_month, fiscal_day_in_month FROM days WHERE PKDate >= @dtStart AND PKDate <= @dtEnd
SELECT @counter = @counter + 1, @fiscal_month = @fiscal_month + 1
end
-- WEEKS
SELECT @counter = 1, @counter_week = 1, @dtEnd = @dtYearStart
WHILE @counter <= 52
begin
SELECT @dtStart = DATEADD(day, 1, @dtEnd)
SELECT @dtEnd = DATEADD(day, 6, @dtStart)
UPDATE days
SET fiscal_week_in_month = @counter_week, fiscal_week_in_year = @counter
FROM days
WHERE PKDate >= @dtStart AND PKDate <= @dtEnd
-- TESTING ONLY, comment the below line out in production
-- SELECT 'Week' as label, PKDate, fiscal_week_in_year, fiscal_week_in_month FROM days WHERE PKDate >= @dtStart AND PKDate <= @dtEnd
SELECT @counter = @counter + 1
-- Get the fiscal month of the row to determine if the month has 4 or 5 weeks.
SELECT @fiscal_month = fiscal_month FROM days WHERE PKDate = @dtStart
SELECT @counter_week = CASE
WHEN @fiscal_month IN (1, 4, 7, 10) AND @counter_week = 5 THEN 1
WHEN @fiscal_month IN (1, 4, 7, 10) AND @counter_week < 5 THEN @counter_week + 1
WHEN @fiscal_month NOT IN (1, 4, 7, 10) AND @counter_week = 4 THEN 1
WHEN @fiscal_month NOT IN (1, 4, 7, 10) AND @counter_week < 4 THEN @counter_week + 1 END
end
-- DAYS
;WITH ro AS (SELECT PKDate, RANK() OVER (ORDER BY PKDate) as row_order FROM days WHERE fiscal_year = @fiscal_year)
UPDATE days
SET fiscal_day_in_year = row_order
FROM days
JOIN ro ON days.PKDate = ro.PKDate
SELECT @counter_year = @counter_year + 1
end
Now the exact same queries as above will return the results for this new fiscal year ...
-- Boundaries of FY 2014
SELECT Min(PKDate) as begin_date, MAX(PKDate) as end_date
FROM days
WHERE fiscal_year = 2014
-- Sales grouped by day for FY 2006
SELECT
CAST(s.OrderDate as date),
SUM(s.OrderQuantity) as order_quantity_sum, SUM(s.SalesAmount) as sales_amount_sum
FROM AdventureWorksDW2012.dbo.FactInternetSales s
JOIN calendar..days d ON s.OrderDate = d.PKDate
WHERE d.fiscal_year = 2006
GROUP BY s.OrderDate
ORDER BY s.OrderDate
-- Sales grouped by month for FY 2006
SELECT
d.fiscal_month,
d.Fiscal_year,
SUM(s.OrderQuantity) as order_quantity_sum, SUM(s.SalesAmount) as sales_amount_sum
FROM AdventureWorksDW2012.dbo.FactInternetSales s
JOIN calendar..days d ON s.OrderDate = d.PKDate
WHERE d.fiscal_year = 2006
GROUP BY d.fiscal_month, d.Fiscal_year
ORDER BY d.fiscal_month
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
Commented: