Con lo años he tenido algunos de los mas extraños y complejos requerimientos de consultas y reportes que te puedas imaginar.
Los más dificiles suelen implicar algun tipo de regla de negocio extraña o requerimiento que incluyen parametros de fechas, logica, rangos, etc. He puesto este código junto con el fin de precalcular muchos elementos de fechas caracterizadas y ser capaz de determinar rapidamente varios valores y caracteristicas de las fechas sin necesidad de romperce la cabeza cada vez que las cosas se ponen dificiles.
Para ejecutarlo, simplemente elije la base de datos en la que desear que se genere la tabla, configura las variables @vDate_Start y @vDate_End con el rango que fechas que quieres poblar en el calendario, y lo ejecutas.
A continuación se muestra un listado de campos de salida y su descripción usando la fecha 10/06/2009 (MM/DD/YYYY) como ejemplo de referencia (el codigo compensara cualquier configuración de la instancia de SQL Server para manipular las configuraciones internas de inicio y fin de semana, dias de las semana, etc). Todo los valores despues del campo calendar_date son especificos para el valor de fecha encontrado en cada registro individual.
- calendar_date: valor de la fecha del calendario (2009-10-06 00:00:00.000)
- calendar_year: año de la fecha (2009)
- calendar_month: mes de la fecha (10)
- calendar_day: día de la fecha (6)
- calendar_quarter: trimestre en el que cae la fecha (4)
- first_day_in_week: primer dia de la semana en el que se encuentra la fecha (2009-10-04 00:00:00.000)
- last_day_in_week: ultimo dia de la semana en que se encuentra la fecha (2009-10-10 00:00:00.000)
- is_week_in_same_month: Esta contenido first_day_in_week y last_day_in_week dentro del mismo mes – Boolean (1)
- first_day_in_month: Primer dia del mes (2009-10-01 00:00:00.000)
- last_day_in_month: Ultimo dia del mes (2009-10-31 00:00:00.000)
- is_last_day_in_month: Es la fecha el ultimo dia del mes (0)
- first_day_in_quarter: Primer dia del trimestre (2009-10-01 00:00:00.000)
- last_day_in_quarter: Ultimo dia del trimestre (2009-12-31 00:00:00.000)
- is_last_day_in_quarter: Es la fecha el ultimo dia del trimestre (0)
- day_of_week: Dia de la semana (3)
- week_of_month: week of the month (2)
- week_of_quarter: Semana del trimestre (2)
- week_of_year: semana del año (41)
- days_in_month: Total de dias en el mes (31)
- month_days_remaining: número de dias que quedan en el mes (25)
- weekdays_in_month: numero de dias de la semana en el mes (22)
- month_weekdays_remaining: numero de dias de la semana restantes en el mes (18)
- month_weekdays_completed: numero de dias de la semana completados en el mes (4)
- days_in_quarter: total de dias en el trimestre (92)
- quarter_days_remaining: numero de dias restantes en el trimestre (86)
- quarter_days_completed: numero de dias completados en el trimestre (6)
- weekdays_in_quarter: numero de dias de la semana en el trimestre (66)
- quarter_weekdays_remaining: numero de dias de la semana restantes en el trimestre (62)
- quarter_days_completed: numero de dias completados en el trimestre (6)
- day_of_year: numero de dias completados en el año (279)
- year_days_remaining: número de dias restantes en el año (86)
- is_weekday: Es la fecha un dia de la semana – Boolean (1)
- is_leap_year: esta la fecha contenida en un año bisiesto – Boolean (0)
- day_name: Nombre completo del dia (Tuesday)
- month_day_name_instance: numero de ocurrencias del day_name dentro del mes hasta e incluyendo la fecha escpecificada (1)
- quarter_day_name_instance: numero de ocurrencias del day_name dentro del trimestre hasta e incluyendo la fecha especificada (1)
- year_day_name_instance: numero de ocurrencias del day_name dentro del año hasta e incluyendo la fecha seleccionada (40)
- month_name: Nombre completo del mes (October)
- year_week: calendar_year y week_of_year concatenado (llenado con ceros a la izquierda) (200941)
- year_month: calendar_year y calendar_month concatenado (llenado con ceros a la izquierda) (200910)
- year_quarter: calendar_year y calendar_quarter concatenados (prefijo «Q») (2009Q4)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
DECLARE @vDate_Start AS DATETIME
DECLARE @vDate_End AS DATETIME
SET @vDate_Start = '01/01/2000'
SET @vDate_End = '12/31/2030'
----------------------------------------------------------------------------------------------------------------------
-- Error Trapping: Check If Permanent Table(s) Already Exist(s) And Drop If Applicable
----------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID ('dbo.date_calendar') IS NOT NULL
BEGIN
DROP TABLE dbo.date_calendar
END
----------------------------------------------------------------------------------------------------------------------
-- Permanent Table: Create Date Xref Table
----------------------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.date_calendar
(
calendar_date DATETIME PRIMARY KEY CLUSTERED
,calendar_year SMALLINT
,calendar_month TINYINT
,calendar_day TINYINT
,calendar_quarter TINYINT
,first_day_in_week DATETIME
,last_day_in_week DATETIME
,is_week_in_same_month INT
,first_day_in_month DATETIME
,last_day_in_month DATETIME
,is_last_day_in_month INT
,first_day_in_quarter DATETIME
,last_day_in_quarter DATETIME
,is_last_day_in_quarter INT
,day_of_week TINYINT
,week_of_month TINYINT
,week_of_quarter TINYINT
,week_of_year TINYINT
,days_in_month TINYINT
,month_days_remaining TINYINT
,weekdays_in_month TINYINT
,month_weekdays_remaining TINYINT
,month_weekdays_completed TINYINT
,days_in_quarter TINYINT
,quarter_days_remaining TINYINT
,quarter_days_completed TINYINT
,weekdays_in_quarter TINYINT
,quarter_weekdays_remaining TINYINT
,quarter_weekdays_completed TINYINT
,day_of_year SMALLINT
,year_days_remaining SMALLINT
,is_weekday INT
,is_leap_year INT
,day_name VARCHAR (10)
,month_day_name_instance TINYINT
,quarter_day_name_instance TINYINT
,year_day_name_instance TINYINT
,month_name VARCHAR (10)
,year_week CHAR (6)
,year_month CHAR (6)
,year_quarter CHAR (6)
);
----------------------------------------------------------------------------------------------------------------------
-- Table Insert: Populate Base Date Values Into Permanent Table Using Common Table Expression (CTE)
----------------------------------------------------------------------------------------------------------------------
WITH cte_date_base_table AS
(
SELECT
@vDate_Start AS calendar_date
UNION ALL
SELECT
DATEADD (DAY, 1, CTE.calendar_date)
FROM
cte_date_base_table CTE
WHERE
DATEADD (DAY, 1, CTE.calendar_date) <= @vDate_End
)
INSERT INTO dbo.date_calendar
(
calendar_date
)
SELECT
CTE.calendar_date
FROM
cte_date_base_table CTE
OPTION
(MAXRECURSION 0)
----------------------------------------------------------------------------------------------------------------------
-- Table Update I: Populate Additional Date Xref Table Fields (Pass I)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.date_calendar
SET
calendar_year = DATEPART (YEAR, calendar_date)
,calendar_month = DATEPART (MONTH, calendar_date)
,calendar_day = DATEPART (DAY, calendar_date)
,calendar_quarter = DATEPART (QUARTER, calendar_date)
,first_day_in_week = DATEADD (DAY, -DATEPART (WEEKDAY, calendar_date)+1, calendar_date)
,first_day_in_month = CONVERT (VARCHAR (6), calendar_date, 112)+'01'
,day_of_week = DATEPART (WEEKDAY, calendar_date)
,week_of_year = DATEPART (WEEK, calendar_date)
,day_of_year = DATEPART (DAYOFYEAR, calendar_date)
,is_weekday = ISNULL ((CASE
WHEN ((@@DATEFIRST-1)+(DATEPART (WEEKDAY, calendar_date)-1))%7 NOT IN (5,6) THEN 1
END),0)
,day_name = DATENAME (WEEKDAY, calendar_date)
,month_name = DATENAME (MONTH, calendar_date)
ALTER TABLE dbo.date_calendar ALTER COLUMN calendar_year INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN calendar_month INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN calendar_day INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN calendar_quarter INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN first_day_in_week DATETIME NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN first_day_in_month DATETIME NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN day_of_week INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN week_of_year INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN day_of_year INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN is_weekday INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN day_name VARCHAR (10) NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN month_name VARCHAR (10) NOT NULL
CREATE NONCLUSTERED INDEX [IX_calendar_year] ON dbo.date_calendar (calendar_year)
CREATE NONCLUSTERED INDEX [IX_calendar_month] ON dbo.date_calendar (calendar_month)
CREATE NONCLUSTERED INDEX [IX_calendar_quarter] ON dbo.date_calendar (calendar_quarter)
CREATE NONCLUSTERED INDEX [IX_first_day_in_week] ON dbo.date_calendar (first_day_in_week)
CREATE NONCLUSTERED INDEX [IX_day_of_week] ON dbo.date_calendar (day_of_week)
CREATE NONCLUSTERED INDEX [IX_is_weekday] ON dbo.date_calendar (is_weekday)
----------------------------------------------------------------------------------------------------------------------
-- Table Update II: Populate Additional Date Xref Table Fields (Pass II)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.date_calendar
SET
last_day_in_week = first_day_in_week+6
,last_day_in_month = DATEADD (MONTH, 1, first_day_in_month)-1
,first_day_in_quarter = A.first_day_in_quarter
,last_day_in_quarter = A.last_day_in_quarter
,week_of_month = DATEDIFF (WEEK, first_day_in_month, calendar_date)+1
,week_of_quarter = (week_of_year-A.min_week_of_year_in_quarter)+1
,is_leap_year = ISNULL ((CASE
WHEN calendar_year%400 = 0 THEN 1
WHEN calendar_year%100 = 0 THEN 0
WHEN calendar_year%4 = 0 THEN 1
END),0)
,year_week = CONVERT (VARCHAR (4), calendar_year)+RIGHT ('0'+CONVERT (VARCHAR (2), week_of_year),2)
,year_month = CONVERT (VARCHAR (4), calendar_year)+RIGHT ('0'+CONVERT (VARCHAR (2), calendar_month),2)
,year_quarter = CONVERT (VARCHAR (4), calendar_year)+'Q'+CONVERT (VARCHAR (1), calendar_quarter)
FROM
(
SELECT
X.calendar_year AS subquery_calendar_year
,X.calendar_quarter AS subquery_calendar_quarter
,MIN (X.calendar_date) AS first_day_in_quarter
,MAX (X.calendar_date) AS last_day_in_quarter
,MIN (X.week_of_year) AS min_week_of_year_in_quarter
FROM
dbo.date_calendar X
GROUP BY
X.calendar_year
,X.calendar_quarter
) A
WHERE
A.subquery_calendar_year = calendar_year
AND A.subquery_calendar_quarter = calendar_quarter
ALTER TABLE dbo.date_calendar ALTER COLUMN last_day_in_week DATETIME NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN last_day_in_month DATETIME NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN first_day_in_quarter DATETIME NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN last_day_in_quarter DATETIME NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN week_of_month INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN week_of_quarter INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN is_leap_year INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN year_week VARCHAR (6) NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN year_month VARCHAR (6) NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN year_quarter VARCHAR (6) NOT NULL
CREATE NONCLUSTERED INDEX [IX_last_day_in_week] ON dbo.date_calendar (last_day_in_week)
CREATE NONCLUSTERED INDEX [IX_year_month] ON dbo.date_calendar (year_month)
CREATE NONCLUSTERED INDEX [IX_year_quarter] ON dbo.date_calendar (year_quarter)
----------------------------------------------------------------------------------------------------------------------
-- Table Update III: Populate Additional Date Xref Table Fields (Pass III)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.date_calendar
SET
is_last_day_in_month = (CASE
WHEN last_day_in_month = calendar_date THEN 1
ELSE 0
END)
,is_last_day_in_quarter = (CASE
WHEN last_day_in_quarter = calendar_date THEN 1
ELSE 0
END)
,days_in_month = DATEPART (DAY, last_day_in_month)
,weekdays_in_month = A.weekdays_in_month
,days_in_quarter = DATEDIFF (DAY, first_day_in_quarter, last_day_in_quarter)+1
,quarter_days_remaining = DATEDIFF (DAY, calendar_date, last_day_in_quarter)
,weekdays_in_quarter = B.weekdays_in_quarter
,year_days_remaining = (365+is_leap_year)-day_of_year
FROM
(
SELECT
X.year_month AS subquery_year_month
,SUM (X.is_weekday) AS weekdays_in_month
FROM
dbo.date_calendar X
GROUP BY
X.year_month
) A
,(
SELECT
X.year_quarter AS subquery_year_quarter
,SUM (X.is_weekday) AS weekdays_in_quarter
FROM
dbo.date_calendar X
GROUP BY
X.year_quarter
) B
WHERE
A.subquery_year_month = year_month
AND B.subquery_year_quarter = year_quarter
ALTER TABLE dbo.date_calendar ALTER COLUMN is_last_day_in_month INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN is_last_day_in_quarter INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN days_in_month INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN weekdays_in_month INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN days_in_quarter INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN quarter_days_remaining INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN weekdays_in_quarter INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN year_days_remaining INT NOT NULL
----------------------------------------------------------------------------------------------------------------------
-- Table Update IV: Populate Additional Date Xref Table Fields (Pass IV)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.date_calendar
SET
month_weekdays_remaining = weekdays_in_month-A.month_weekdays_remaining_subtraction
,quarter_weekdays_remaining = weekdays_in_quarter-A.quarter_weekdays_remaining_subtraction
FROM
(
SELECT
X.calendar_date AS subquery_calendar_date
,ROW_NUMBER () OVER
(
PARTITION BY
X.year_month
ORDER BY
X.calendar_date
) AS month_weekdays_remaining_subtraction
,ROW_NUMBER () OVER
(
PARTITION BY
X.year_quarter
ORDER BY
X.calendar_date
) AS quarter_weekdays_remaining_subtraction
FROM
dbo.date_calendar X
WHERE
X.is_weekday = 1
) A
WHERE
A.subquery_calendar_date = calendar_date
----------------------------------------------------------------------------------------------------------------------
-- Table Update V: Populate Additional Date Xref Table Fields (Pass V)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.date_calendar
SET
month_weekdays_remaining = A.month_weekdays_remaining
,quarter_weekdays_remaining = A.quarter_weekdays_remaining
FROM
(
SELECT
X.calendar_date AS subquery_calendar_date
,COALESCE (Y.month_weekdays_remaining, Z.month_weekdays_remaining, X.weekdays_in_month) AS month_weekdays_remaining
,COALESCE (Y.quarter_weekdays_remaining, Z.quarter_weekdays_remaining, X.weekdays_in_quarter) AS quarter_weekdays_remaining
FROM
dbo.date_calendar X
LEFT JOIN dbo.date_calendar Y ON DATEADD (DAY, 1, Y.calendar_date) = X.calendar_date
AND Y.year_month = X.year_month
LEFT JOIN dbo.date_calendar Z ON DATEADD (DAY, 2, Z.calendar_date) = X.calendar_date
AND Z.year_month = X.year_month
WHERE
X.month_weekdays_remaining IS NULL
) A
WHERE
A.subquery_calendar_date = calendar_date
ALTER TABLE dbo.date_calendar ALTER COLUMN month_weekdays_remaining INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN quarter_weekdays_remaining INT NOT NULL
----------------------------------------------------------------------------------------------------------------------
-- Table Update VI: Populate Additional Date Xref Table Fields (Pass VI)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.date_calendar
SET
is_week_in_same_month = A.is_week_in_same_month
,month_days_remaining = days_in_month-calendar_day
,month_weekdays_completed = weekdays_in_month-month_weekdays_remaining
,quarter_days_completed = days_in_quarter-quarter_days_remaining
,quarter_weekdays_completed = weekdays_in_quarter-quarter_weekdays_remaining
,month_day_name_instance = A.month_day_name_instance
,quarter_day_name_instance = A.quarter_day_name_instance
,year_day_name_instance = A.year_day_name_instance
FROM
(
SELECT
X.calendar_date AS subquery_calendar_date
,ISNULL ((CASE
WHEN DATEDIFF (MONTH, X.first_day_in_week, X.last_day_in_week) = 0 THEN 1
END),0) AS is_week_in_same_month
,ROW_NUMBER () OVER
(
PARTITION BY
X.year_month
,X.day_name
ORDER BY
X.calendar_date
) AS month_day_name_instance
,ROW_NUMBER () OVER
(
PARTITION BY
X.year_quarter
,X.day_name
ORDER BY
X.calendar_date
) AS quarter_day_name_instance
,ROW_NUMBER () OVER
(
PARTITION BY
X.calendar_year
,X.day_name
ORDER BY
X.calendar_date
) AS year_day_name_instance
FROM
dbo.date_calendar X
) A
WHERE
A.subquery_calendar_date = calendar_date
ALTER TABLE dbo.date_calendar ALTER COLUMN is_week_in_same_month INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN month_days_remaining INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN month_weekdays_completed INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN quarter_days_completed INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN quarter_weekdays_completed INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN month_day_name_instance INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN quarter_day_name_instance INT NOT NULL
ALTER TABLE dbo.date_calendar ALTER COLUMN year_day_name_instance INT NOT NULL
----------------------------------------------------------------------------------------------------------------------
-- Main Query: Final Display/Output
----------------------------------------------------------------------------------------------------------------------
SELECT
URD.*
FROM
dbo.date_calendar URD
ORDER BY
URD.calendar_date
