En ocasiones necesitamos generar secuencias numéricas o de fechas para diferentes propósitos. En este caso vamos a ver un script para generar una secuencia de fechas y aprovechar diferentes funciones del sistema para llenar una tabla de dimensión de tiempo de un datamart.

 

Para ello vamos a usar Common Table Expresions o CTE recursivo (). La recursividad nos permite iterar para generar la secuencia. Vamos a mostrar el script () y a explicar de qué se trata:

 

–define límites de inicio y fin

DECLARE @FechaDesde date, @FechaHasta date

SELECT @FechaDesde=‘2009-01-01’, @FechaHasta=‘2015-12-31’;

 

–Loop para generar las fechas

WITH DateSequence(Fecha) AS

(    SELECT @FechaDesde as Fecha

        UNION ALL                                           –>(1)

    SELECT DATEADD(DAY, 1, Fecha)

    FROM DateSequence

    WHERE Fecha < @FechaHasta

)

–select final para obtener la secuencia

SELECT                                                      –>(2)

  Fecha,

  YEAR(Fecha) AS AñoNum,

  MONTH(Fecha) AS MesNum,

  YEAR(Fecha) AS AñoChar,

  RIGHT(‘0’+CAST(MONTH(Fecha) as varchar(2)),2) AS MesChar, –>(3)

  DATENAME(DW,Fecha) AS NombreDia,                          –>(4)

  DATENAME(DAYOFYEAR,Fecha) AS DiaDelAño,

  DATENAME(MONTH,Fecha) AS NombreMes,

  DATENAME(WEEK,Fecha) AS SemanaAño,

  DATENAME(QQ,Fecha) AS Trimestre,

  Case                                                      –>(5)

   WHEN MONTH(Fecha)>=1 AND MONTH(Fecha)<=6 THEN 1

   WHEN MONTH(Fecha)>=7 AND MONTH(Fecha)<=12 THEN 2

  end Semestre

FROM DateSequence OPTION (MAXRECURSION 3000)                –>(6)

GO

 

Como vemos, primero tenemos la definición de variables necesarias para el proceso. Luego tenemos la definición del CTE que comienza con la cláusula WITH y por último el query que utiliza los datos generados en el CTE y aprovecha diferentes funciones del sistema para completar información de la dimensión tiempo. A la derecha hay varios números que indican algunos puntos que deseo resaltar:

 

(1)    Este es el query recursivo dentro del CTE. Hay dos consultas unidas a través de la cláusula UNION ALL. En el primer SELECT se obtiene el valor de @FechaDesde con el alias Fecha y en el segundo SELECT se le añade un día al valor de Fecha utilizando la función DATEADD (), con una condición WHERE que establece como limite el valor de @FechaHasta. Esto va a hacer que obtengamos una iteración desde la fecha de inicio hasta la fecha de fin, avanzando día por día. Cambiando la función DATEADD puedo variar el intervalo a mes o año, según se requiera.

 

(2)    En este caso estamos ejecutando una consulta con SELECT, la cual aprovecha los datos generados en el CTE. Ojo que el CTE solo bota el valor Fecha, el cual se aprovecha para obtener más información.

 

(3)    Se ha diseñado la consulta para obtener el dato de mes de forma numérica y carácter. En esta línea se formatea el dato carácter añadiendo un 0 a la izquierda para uniformizar el dato, pero teniendo la precaución de que todos los meses tengan una longitud de dos caracteres. Así, octubre, noviembre y diciembre no llevan 0 a la izquierda.

 

(4)    Con la función DATENAME podemos obtener varias formas de representar el valor de Fecha. Tenerlo ya almacenado en la tabla de dimensión nos permite utilizarlo directamente en un cubo o en una consulta sin necesidad de estar aplicando constantemente funciones. Recuerden que el objetivo de un datamart es estar optimizado para la velocidad de las consultas.

 

(5)    No encontré una forma de obtener el semestre utilizando DATENAME así que trabajé una sentencia Case utilizando como condición el mes de la fecha.

 

(6)    MAXRECURSION () es necesario en este query. Por defecto los CTE recursivos solo iteran 100 veces y aquí obtenemos más de 2000 fechas. Prueben quitando esta cláusula y verán como sale un error.

 

El resultado de ejecutar el script es el siguiente (se muestran solo las primeras líneas):

 

NOTA: Si los nombres de los días o meses aparecen en ingles, ejecutar el siguiente comando: SET LANGUAGE SPANISH

 

 

Si deseamos utilizar la secuencia para insertar los datos en una tabla, solo ha que incluir una sentencia INSERT en la consulta que utiliza el CTE. Para hacer la prueba, primero crearemos una tabla:

 

CREATE TABLE dbo.dTiempo(

IdFecha smallint IDENTITY(1,1) not null, –Columna con identidad

Fecha date not null,                     –fecha DD/MM/AAA

AñoNum int not null,                     –Año en formato numérico

MesNum tinyint not null,                 –Mes en formato numérico

AñoChar char(4) not null,                –Año en formato caracter

MesChar char(2) not null,                –Mes en formato caracter

NombreDia varchar(10) not null,          –Nombre del día en letras

DiaDelAño smallint not null,             –Numero de día en todo el año

NombreMes varchar(10) not null,          –Nombre del mes en letras

SemanaDelAño tinyint not null,           –Numero de semana del año

Trimestre tinyint not null,              –Numero de trimestre

Semestre tinyint not null)               –Numero de semestre

GO

 

ALTER TABLE dbo.dTiempo

ADD CONSTRAINT PK_dTiempo

PRIMARY KEY (IdFecha)

GO

 

Una vez creada la tabla ejecutamos el mismo script con la sentencia INSERT en el query:

 

NOTA: cuidado al ejecutar repetidamente este script por que puede crear registros duplicados

 

–define límites de inicio y fin

DECLARE @FechaDesde date, @FechaHasta date

SELECT @FechaDesde=‘2009-01-01’, @FechaHasta=‘2015-12-31’;

 

–Loop para generar las fechas

WITH DateSequence(Fecha) AS

(    SELECT @FechaDesde as Fecha

        UNION ALL                                          

    SELECT DATEADD(DAY, 1, Fecha)

    FROM DateSequence

    WHERE Fecha < @FechaHasta

)

–select final para insertar los registros

INSERT INTO dbo.dTiempo                           –>Sentencia Insert

SELECT                                                     

  Fecha,

  YEAR(Fecha) AS AñoNum,

  MONTH(Fecha) AS MesNum,

  YEAR(Fecha) AS AñoChar,

  RIGHT(‘0’+CAST(MONTH(Fecha) as varchar(2)),2) AS MesChar,

  DATENAME(DW,Fecha) AS NombreDia,                         

  DATENAME(DAYOFYEAR,Fecha) AS DiaDelAño,

  DATENAME(MONTH,Fecha) AS NombreMes,

  DATENAME(WEEK,Fecha) AS SemanaAño,

  DATENAME(QQ,Fecha) AS Trimestre,

  Case                                                     

   WHEN MONTH(Fecha)>=1 AND MONTH(Fecha)<=6 THEN 1

   WHEN MONTH(Fecha)>=7 AND MONTH(Fecha)<=12 THEN 2

  end Semestre

FROM DateSequence OPTION (MAXRECURSION 3000)               

GO

 

Los animo a que modifiquen el script para obtener otros datos de fecha o también obtener secuencia numéricas.