Calendario de año fiscal en SQL Server
Avatar Ven
Pregunta contestada

Calendario de año fiscal en SQL Server

Tengo un requisito para crear un calendario de año fiscal con

start date = 2019-03-31 and end_date = 2020-03-28

declare @startdate date = '2019-03-30'
declare @enddate date = '2020-03-28'
declare @dates table (date date, MonthNo int)
while @startdate < @enddate
BEGIN
set @startdate = dateadd(dd,1,@startdate)
insert @dates (date)
select  @startdate
END
select * from @dates 

Tendré que completar la columna del número de mes

MonthNumber: en este caso, no se puede obtener a través de una consulta típica usando

DATEPART(MONTH, [date])

Requisito: el número de mes debe comenzar en 1 y debe asignarse como se muestra a continuación

NoOfDays    trading_month
28            1
28            2
35            3
28            4
28            5
35            6
28            7
28            8
35            9
28           10
28           11
35           12

Ej: El mes de negociación = 1 está entre 2019-03-31 and 2019-04-28 y los próximos 28 días = mes 2 y los próximos 35 días mes = 3, etc.

ps: no quiero usar la declaración de caso 12 veces para completar el número de mes, ¿hay alguna forma alternativa de lograr esto? puede lograr de esta manera

select *,  case when ROW_NUMBER() over (order by date) between 1 and  28 then 1 
                when ROW_NUMBER() over (order by date) between 29 and 28+28 then 2
                when ROW_NUMBER() over (order by date) between 28+28+1 and  28+29+35 then 3 
                ----so on 
                end MonthNo
from @dates
Mostrar la mejor respuesta

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

Avatar Ven

Gracias por esto, ya lo he visto. Esto no resuelve este problema en particular. Estoy tratando de encontrar una manera en lugar de codificar usando el caso

Supongo que no, ya que cuando construí la tabla de calendario Crop Year también era un año 4-4-5, y tenía que hacerlo con bloques CASE dentro de un bucle. En el artículo Años fiscales, desplácese hacia abajo hasta 'Hard: 4-4-5 Año fiscal' y verá lo que quiero decir.

Avatar Ven

Este es un gran trabajo, vi tu blog. Aceptaré esta respuesta ya que ayuda a crear otras columnas y gracias por esto.

No hay problema. Si se le ocurre alguna mejora, hágamelo saber y mejoraré mi artículo. En este momento, lo único que tengo en proyecto para otro artículo sería un cte rápido y sucio para hacer un conjunto de calendario simple donde meses = meses calendario.

No estoy seguro de entender exactamente su objetivo aquí, por qué 1 = 28 específicamente. Sin embargo, parece que su idea de parte de la fecha estaba en línea con la solución deseada si solo hace esto, obtengo el diseño que deseaba.

declare @startdate date = '2019-03-30'
declare @enddate date = '2020-03-28'
declare @dates table (NoOfDays date, trading_mo int)
while @startdate < @enddate
BEGIN
set @startdate = dateadd(dd,1,@startdate)
insert @dates (NoOfDays)
select  @startdate
END
select count(NoOfDays) noOfDays, 
    datepart(month,NoOfDays) trading_mo 
from @dates 
group by datepart(month,NoOfDays)
order by trading_mo

Avatar Ven

Estoy seguro de que no has entendido la pregunta. Los primeros 28 días deben adquirir el mes número 1 y los siguientes 28 días el mes número 2 y los próximos 35 días el mes número 3 y así sucesivamente como se menciona en la pregunta anterior.

sí, leí que su pregunta modificada es muy diferente ahora, pero no tengo otra alternativa fuera de Jims

Avatar Ven

Es posible que desee leer la respuesta de John anterior, podría ser útil con diferentes requisitos comerciales

Sí, la unión de uniones cruzadas vinculadas al valor fue creativa, también lo aprobaré.

Esta es una opción.

Ejemplo

Declare @Date1 date = '2019-03-31'


Select [Date]=DateAdd(DAY,Row_Number() over (Order by M,D)-1,@Date1)
      ,[Year]=DatePart(YEAR,@Date1)
      ,[Month]=M
 From (
         Select *
          From (Select Top (28) D=Row_Number() Over (Order By (Select Null)) From  master..spt_values n1  ) A 
         Cross Join  (Values (1),(2),(4),(5),(7),(8),(10),(11)) B(M)
        Union All 
         Select *
          From ( Select Top (35) D=Row_Number() Over (Order By (Select Null)) From  master..spt_values n1 ) A 
         Cross Join  (Values (3),(6),(9),(12)) B(M)
      ) A
 Order by 1,2

Devoluciones

Date        Year    Month
2019-03-31  2019    1
2019-04-01  2019    1
2019-04-02  2019    1
2019-04-03  2019    1
2019-04-04  2019    1
2019-04-05  2019    1
2019-04-06  2019    1
2019-04-07  2019    1
2019-04-08  2019    1
2019-04-09  2019    1
...
2020-03-21  2019    12
2020-03-22  2019    12
2020-03-23  2019    12
2020-03-24  2019    12
2020-03-25  2019    12
2020-03-26  2019    12
2020-03-27  2019    12
2020-03-28  2019    12

Avatar Ven

Esto tiene mucho sentido, ¿puede explicarme cómo leer la pregunta, Jim y John, respuestas perfectas? Gracias por esto

@Ven feliz de ayudar. He visto patrones como este antes, 15 años en la industria bancaria, puedes ver cuántas organizaciones funcionan.

Avatar Ven

exactamente John, cada organización es diferente, mucha gente piensa que la tabla de fechas SQL es tan fácil como por qué y cómo, en lugar de ajustarse a los requisitos comerciales. Gracias una vez más

Pude obtener los resultados esperados con la siguiente consulta. La única adición a la tabla original fue una columna de identidad. Usando la mayor parte de su código original:

declare @startdate date = '2019-03-30'
declare @enddate date = '2020-03-28'
declare @dates table (pkindex int IDENTITY(1,1), [date] date, MonthNo tinyint)
while @startdate < @enddate
BEGIN
set @startdate = dateadd(dd,1,@startdate)
insert @dates ([date])
select  @startdate       
END

DECLARE @requirments TABLE (NoOfDays tinyint, trading_month tinyint)
INSERT INTO @requirments VALUES
 (28, 1), (28, 2), (35, 3), (28, 4), (28, 5), (35, 6), (28, 7), (28, 8), (35, 9)
,(28, 10), (28, 11), (35, 12)


UPDATE @dates
   SET MonthNo = 
       (SELECT MIN(R.trading_month)
          FROM @requirments R
         WHERE pkindex < 
               (SELECT SUM(R2.NoOfDays)
                  FROM @requirments R2
                 WHERE R2.trading_month < R.trading_month + 1
               ) + 1
       )

SELECT * FROM @dates