El siguiente código lo genere para que mis amigos del Nacional Monte de Piedad y tiene la lógica necesaria para generar un calendario de fechas desde 1901 hasta 2020, incluyendo los feriados obligatorios y no obligatorios de ley (Día de todos los Santos, Día de Muertos, Aniversario de la Batalla Puebla, Aniversario de la Revolución Mexicana, Natalicio de Benito Juarez, Navidad, Día de la Raza, Día de la Bandera, Día de Independencia, Día del Trabajo, Año Nuevo, Día de la Virgen de Guadalupe, Día de la Madre, Aniversario de la Constitución), semana santa (jueves santo, viernes santo, sábado santo y pascua), y el día del traslado del poder ejecutivo de ley.
El código se complica por:
- Desde año 2006 algunos de los feriados son fijos y otros se pasan a un lunes específico dentro del mes, por ejemplo el tercer lunes de noviembre.
- Los cálculos para la semana santa, son complejos.
- La transmisión del Poder Ejecutivo ocurre cada seis años en una fecha fija.
El código puede ser limpiado, para ser simplificado y probablemente requiere ser más comentado, NUEVA VERSION.
SET LANGUAGE Spanish;
GO
CREATE FUNCTION dbo.Pascua (@Yr as int)
RETURNS DATETIME
AS
BEGIN
— Codigo encontrado en: http://www.tek-tips.com/faqs.cfm?fid=5075
Declare @Cent int, @I int, @J int, @K int, @Metonic int, @EMo int, @EDay int
Set @Cent=@Yr/100
Set @Metonic=@Yr % 19
Set @K=(@Cent–17)/25
Set @I=(@Cent–@Cent/4-(@Cent–@K)/3+19*@Metonic+15) % 30
Set @I=@I-(@I/28)*(1-(@I/28)*(29/(@I+1))*((21–@Metonic)/11))
Set @J=(@Yr+@Yr/4+@I+2–@Cent+@Cent/4) % 7
Set @EMo=3+(@I–@J+40)/44
Set @EDay=@I–@J+28–31*(@EMo/4)
RETURN CAST(CAST(@Yr*10000+@Emo*100+@Eday AS VARCHAR(8)) AS DATETIME);
/*This algorithm is from the work done by JM Oudin in 1940 and is accurate from year 1754 to 3400.*/
END;
GO
— Codigo para llenar fecha de dimension Fecha en Mexico.
; WITH Fechas(Fecha, DimFechaId) AS
(
SELECT CAST(‘19010101’ AS DATETIME)
, YEAR(CAST(‘19010101’ AS DATETIME))*10000
+MONTH(CAST(‘19010101’ AS DATETIME))*100
+DAY(CAST(‘19010101’ AS DATETIME))
UNION ALL
SELECT DATEADD(DAY, 1, Fecha), YEAR(DATEADD(DAY, 1, Fecha))*10000+MONTH(DATEADD(DAY, 1, Fecha))*100+DAY(DATEADD(DAY, 1, Fecha))
FROM Fechas
WHERE DATEADD(DAY, 1, Fecha)<‘20210101’
)
, Festividades (Festividad, Festivo, TipoFeriado, Dia, Lunes)
AS( SELECT ‘Dia de todos los Santos’ ,‘Festivo NO Obligatorio’, ‘NoAplica’, 1101, 0
UNION ALL SELECT ‘Dia de Muertos’ ,‘Festivo NO Obligatorio’, ‘NoAplica ‘, 1102, 0
UNION ALL SELECT ‘Aniversario de la Batalla Puebla’ ,‘Festivo NO Obligatorio’, ‘NoAplica’, 505, 0
UNION ALL SELECT ‘Aniversario de la Revolución Mexicana’ ,‘Festivo Obligatorio’, ‘Pasar’, 1120, 3
— 3er Lunes Noviembre
UNION ALL SELECT ‘Natalicio de Benito Juarez’ ,‘Festivo Obligatorio’, ‘Pasar’, 321, 3
— 3er Lunes Marzo
UNION ALL SELECT ‘Navidad’, ‘Festivo Obligatorio’ ,‘Fijo’, 1225, 0
UNION ALL SELECT ‘Dia de la Raza’ ,‘Festivo NO Obligatorio’, ‘NoAplica’, 1012, 0
UNION ALL SELECT ‘Dia de la Bandera’ ,‘Festivo NO Obligatorio’, ‘NoAplica’, 224, 0
UNION ALL SELECT ‘Dia de Independencia’ ,‘Festivo Obligatorio’, ‘Fijo’, 916, 0
UNION ALL SELECT ‘Dia del Trabajo’, ‘Festivo Obligatorio’ ,‘Fijo’, 501, 0
UNION ALL SELECT ‘Año Nuevo’ ,‘Festivo Obligatorio’, ‘Fijo’, 101, 0
UNION ALL SELECT ‘Día de la Virgen de Guadalupe’ ,‘Festivo Obligatorio’, ‘Fijo’, 1212, 0
UNION ALL SELECT ‘Dia de la Madre’ ,‘Festivo NO Obligatorio’, ‘NoAplica’, 510, 0
UNION ALL SELECT ‘Aniversario de la Constitucion’ ,‘Festivo Obligatorio’, ‘Pasar’, 205, 1
— Primer Lunes de Febrero
)
SELECT DimFechaId
, Fechas.Fecha AS Fecha
, (DimFechaId/10000)*10000+101 AS AnioId
, CAST(Year(Fechas.Fecha) AS CHAR(4)) AS AnioNombre
, 300*(DimFechaId/300)+1 AS TrimestreId
, ‘Q’+CAST(DATEPART(Quarter, Fechas.Fecha) AS VARCHAR(1))
+ ‘-‘ +CAST(Year(Fechas.Fecha) AS CHAR(4)) AS TrimestreNombre
, (DimFechaId/100)*100+1 AS MesId
, CAST(DATENAME(Month, Fechas.Fecha) AS VARCHAR(25))+ ‘-‘
+ CAST(Year(Fechas.Fecha) AS CHAR(4)) AS MesNombre
, YEAR(DATEADD(DAY, 1, DATEADD(DAY, –DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) ))*10000
+MONTH(DATEADD(DAY, 1, DATEADD(DAY, –DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) ))*100
+DAY(DATEADD(DAY, 1, DATEADD(DAY, –DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) )) AS SemanaId
, ‘S’+RIGHT(‘0’+CAST(DATENAME(Week, Fechas.Fecha) AS VARCHAR(25)),2)+ ‘-‘
+ CAST(Year(Fechas.Fecha) AS CHAR(4)) AS SemanaName
, DATEPART(Weekday, Fechas.Fecha) AS DiaSemanaID
, DATENAME(Weekday, Fechas.Fecha) AS DiaSemanaNombre
, CASE
WHEN DATEPART(Weekday, Fechas.Fecha) IN (6,7) THEN ‘FinSemana’
ELSE ‘EntreSemana’
END AS DiaEntreFinSemana
, CASE
WHEN YEAR(Fechas.Fecha)<1991 THEN ‘No Aplica’
WHEN DATEPART(Weekday, Fechas.Fecha) IN (7) THEN ‘NO Hábil’
WHEN Fechas.Fecha BETWEEN DATEADD(day, –2, dbo.Pascua(YEAR(Fechas.Fecha)))
AND dbo.Pascua(YEAR(Fechas.Fecha)) THEN ‘NO Hábil’
WHEN (Fechas.DimFechaId%10000)=1201 — Primero de Diciembre
AND ((Fechas.DimFechaId/10000)-1994)%6=0 — 1 de Cada 6 años
THEN ‘NO Hábil’
WHEN Festividades.Festivo=‘Festivo Obligatorio’
AND (Festividades.TipoFeriado=‘Fijo’ or YEAR(Fechas.Fecha)<=2006)
— Nueva Ley Aplica del 2006 en Adelante
THEN ‘NO Hábil’
WHEN FLunes.Festivo=‘Festivo Obligatorio’
AND DATEPART(Weekday, Fechas.Fecha) =1
THEN ‘NO Hábil’
WHEN FLunes.Festivo=‘Festivo Obligatorio’
AND Festividades.TipoFeriado=‘Pasar’
AND YEAR(Fechas.Fecha)>2006
THEN ‘NO Hábil’
ELSE ‘Hábil’
END AS DiaHabilFeriadoCorp
, CASE
WHEN YEAR(Fechas.Fecha)<1991 THEN ‘No Aplica’
WHEN dbo.Pascua(YEAR(Fechas.Fecha))=Fechas.Fecha THEN ‘Pascua’
WHEN Fechas.Fecha BETWEEN DATEADD(day, –1, dbo.Pascua(YEAR(Fechas.Fecha)))
AND DATEADD(day, –3, dbo.Pascua(YEAR(Fechas.Fecha)))
THEN DATENAME(Weekday, Fechas.Fecha) + ‘ Santo’
WHEN (Fechas.DimFechaId%10000)=1201 — Primero de Diciembre
AND ((Fechas.DimFechaId/10000)-1994)%6=0 — 1 de Cada 6 años
THEN ‘Transmisión del Poder Ejecutivo Federal’
ELSE COALESCE(Festividades.Festividad
, FLunes.Festividad
, ‘NO Festivo’) END AS Festividad
, CASE
WHEN YEAR(Fechas.Fecha)<1991 THEN ‘No Aplica’
WHEN Fechas.Fecha BETWEEN DATEADD(day, –2, dbo.Pascua(YEAR(Fechas.Fecha)))
AND dbo.Pascua(YEAR(Fechas.Fecha)) THEN ‘Festivo Obligatorio’
WHEN (Fechas.DimFechaId%10000)=1201 — Primero de Diciembre
AND ((Fechas.DimFechaId/10000)-1994)%6=0 — 1 de Cada 6 años
THEN ‘Festivo Obligatorio’
WHEN Festividades.Festivo IS NOT NULL and Festividades.TipoFeriado=‘Pasar’
THEN ‘Festivo Pasado’
WHEN Festividades.Festivo IS NOT NULL
THEN Festividades.Festivo
WHEN FLunes.Festivo IS NOT NULL
THEN ‘Lunes: Festivo Pasado’
ELSE ‘NO Festivo’ END AS TipoFestivoFestivo
FROM Fechas
LEFT JOIN Festividades
ON (Fechas.DimFechaId % 10000)=Festividades.Dia
AND YEAR(Fechas.Fecha)>=1990
LEFT JOIN Festividades AS FLunes
ON DATEPART(Weekday, Fechas.Fecha)=1 — Dia Lunes
AND FLunes.TipoFeriado=‘Pasar’ — Que el Feriado sea Pasar
AND FLunes.Dia/100=month(Fechas.Fecha) — Que sea el mismo Mes
AND YEAR(Fechas.Fecha)>2006
AND DATEADD(DAY, ((8–DATEPART(WEEKDAY, CAST(CAST(YEAR(Fechas.Fecha)*10000
+(FLunes.Dia/100)*100+1 AS CHAR(8)) AS SMALLDATETIME)))%7)+ ((FLunes.Lunes–1)*7)
, CAST(CAST(YEAR(Fechas.Fecha)*10000+(FLunes.Dia/100)*100+1 AS CHAR(8)) AS SMALLDATETIME))
=Fechas.Fecha
AND DATEPART(Weekday
, CAST(CAST( FLunes.Dia+10000*YEAR(Fechas.Fecha) as varchar(8)) AS SMALLDATETIME))
NOT IN (1, 6,7)
ORDER BY Fechas.Fecha
OPTION (MAXRECURSION 0)
Definitivamente el lenguaje SQL no fue creado para hacer este tipo de consultas!
1 comment
Tiene un error este script: no muestra el tercer lunes del mes de marzo.