Siguiendo con nuestra serie, hoy vamos a ver otro punto de vista, aún seguiremos usando CTE’s aunque el objetivo final es bien diferente, el objetivo es simplemente rellenar una tabla con números que después usaremos desde una vista, de esta forma podríamos usar esta vista para crear una dimensión de tipo tiempo. Esto es al fin y al cabo el objetivo de esta serie de artículos. Para hacerlo vamos a necesitar una tabla con los números del 1 al 10.000 y ahí es donde entra a jugar nuestra CTE, es una bastante típica, solamente que esta vez la usaremos para insertar esos diez mil registros. Vamos a ver un poquito de código que sirve para esto:

create table nums(id int not null primary key);
with mycte(id) as  (
 select 1 id
  union all 
select id+1 from mycte where id<10000  )
insert into nums (id)
select id from mycte option(maxrecursion 0);
 
 

Una vez que tenemos este código listo y por tanto tenemos nuestra tabla nums que contiene los valores del 1 al 10.000 vamos a usarla en una vista, la idea es bien sencilla, lo que haremos será elegir una fecha de inicio y sumar el valor de id, eso nos sumará el número de dias que estamos buscando. De esta forma tendremos 10.000 fechas disponibles lo que son haciendo una simple división más de 27 años. La idea de la vista es hacer join de una select que solo devolverá la fecha de inicio con la tabla de nums, obviamente no tenemos cláusula por la que unir pero eso no es problema, podemos usar un CROSS JOIN que no necesita claúsula ON. Veamos el código que nos servirá de base para nuestra vista.

SELECT

�

FECHA+id as Fecha FROM ( SELECT CAST('20000101' AS DATETIME) FECHA) t CROSS JOIN nums 

Esta consulta devuelve lo que puedes ver en esta imagen:

Esta es la idea obviamente, pero necesitamos  construir una vista que nos devuelva un resultado similar al que obteniamos en el artículo 1 de esta serie, la connotación aquí es ligeramente distinta porque no podemos asegurar en que valor está establecido el set datefirst ni el valor del lenguaje, por lo que resulta poco predecible,  aún así aquí teneis el código y una imagen del resultado…

CREATE
 

VIEW DimTiempo as 
select Fecha, 
CAST(CONVERT(varchar(10),fecha,112) as int) FechaKey,
YEAR(Fecha) as Año,
MONTH(Fecha) as Numero_Mes,
DATENAME(month,fecha) Mes_Castellano,
DATENAME(month,fecha) +' '+ cast(YEAR(fecha) as varchar(4)) Mes_ao_Castellano,
year(fecha)*100+month(fecha) MesKey,
DATEPART(DW,fecha) Semana,
year(FEcha)*100 + DATEPART(DW,fecha) SemanaKey,
cast(DAY(fecha) as varchar(10)) + ' de ' + DATENAME(MM,fecha) + ' de ' + cast(YEAR(fecha) as varchar(4)) Fecha_Castellano,
case when datepart(weekday,fecha)>5 then 'Fin de semana' else 'Semana' end TipoDia,
datepart(dayofyear,fecha) DiaDelAño,
DATEPART(DW,fecha) DiaDeLaSemana,
DATEPART(dd,fecha) DiaDelMes,
DATENAME(dw,fecha) Dia_castellano 
from 
(select CAST('20000101' as datetime) fecha ) tabla cross join nums

Esta vista devuelve el resultado que podeis ver en la siguiente imagen.

En la siguiente parte de esta serie, materializaremos esta vista en una tabla y pondremos tres idiomas dentro de nuestra tabla de tiempo, también generaremos un procedimiento almacenado para que todo esto se vea relleno. Al final de la serie pondremos una serie de artículos colgaré todos los script para que sirvan de referencia.