Qué suerte... Descarga y ejecuta el código de mi artículo SQL Server Calendar Table, luego descargue y modifique el código en Tabla de calendario de SQL Server: Años fiscales para ajustarse a su definición de un año fiscal y ejecutarlo.
Una vez tuve un cliente que se ejecutaba en 'Año de cultivo' donde sus años fiscales comenzaban la última semana de mayo, y la única forma de lograrlo era crear una carga de T-SQL para completar una tabla de calendario. Una vez que se hizo eso para cualquier conjunto de datos dado, todo lo que tenía que hacer era UNIRSE en la columna de fecha y luego podía obtener todos los valores de columna relacionados con impuestos que quería sin tener que volver a calcularlos cada vez.
{editar: Aquí está el código relevante que crea el 4-4-5 del artículo anterior de Semanas fiscales}
USE calendar
GO
/*
Calendar table: Populate the six fiscal_ columns
2015-09-16
*/
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
Buena suerte.
Jim
