Resulta que no me acordaba que publicamos este video en el canal de youtube de Solid Quality Mentors, y lo he visto en SQLServerSI, el sitio de mi amigo y compañero y MVP Salva Ramos, yo dejé de pertenecer a ese increible grupo de gente en septiembre. Pero mi corazón está con ellos.Relaciones entre Atributos en youtube

Empezaremos este tercer artículo de la serie simplemente creando una tabla que alojará nuestros datos de tiempo en distintos idiomas, una vez rellenada esta tabla, puede servirnos perfectamente de base para la creación de una dimensión tiempo, que será el siguiente artículo, en el que comenzaremos a crear el proyecto de Analisys Services para construir la dimensión tiempo.

La tabla contiene la estrucutra básica para contener todos los datos necesarios relacionados con el tiempo , y además tiene la versión en castellano, francés e ingles de estos datos

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

CREATE TABLE [Dimensiones].[Tiempo](
[FechaKey] [int] not NULL primary key ,
[Fecha] [datetime] NULL,
[Año] [int] NULL,
[Numero_Mes] [int] NULL,
[Mes_Castellano] [nvarchar](30) NULL,
[Mes_año_Castellano] [nvarchar](35) NULL,
[MesKey] [int] NULL,
[Semana] [int] NULL,
[SemanaKey] [int] NULL,
[Fecha_Castellano] [nvarchar](52) NULL
[TipoDia] [varchar](13) NOT NULL,
[DiaDelAño] [int] NULL,
[DiaDeLaSemana] [int] NULL,
[DiaDelMes] [int] NULL,
[Dia_castellano] [nvarchar](30) NULL,<code>[Mes_Inglés] [nvarchar](30) NULL,
[Mes_año_Inglés] [nvarchar](35) NULL,
[Fecha_Inglés] [nvarchar](52) NULL,
[Dia_Inglés] [nvarchar](30) NULL,
 
[Mes_Francés] [nvarchar](30) NULL,
[Mes_año_Francés] [nvarchar](35) NULL,
[Fecha_Francés] [nvarchar](52) NULL,
[Dia_Francés] [nvarchar](30) NULL
 
) ON [PRIMARY]
 
GO

Una vez que tenemos la tabla tenemos que rellenarla para ello usaremos el mismo código que la vista del artículo anterior. En este caso si podemos utilizar las cláusulas que hablamos en el primer artículo SET DATE FIRST y SET LANGUAGE, de forma que todo esté adecuadamente configurado cuando hagamos los inserts y los updates corresponeientes. Veamnos como rellenar la tabla con las fechas que deseamos.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

set language spanish;
set datefirst 1;
insert into dimensiones.tiempo
(FechaKey, Fecha, Año, Numero_Mes, Mes_Castellano, Mes_año_Castellano, MesKey, Semana, SemanaKey, Fecha_Castellano,
TipoDia, DiaDelAño, DiaDeLaSemana, DiaDelMes, Dia_castellano)
select
CAST(CONVERT(varchar(10),fecha + id ,112) as int) FechaKey,
fecha+id as Fecha,
YEAR(fecha+ID) as año,
MONTH(fecha+id) as Numero_Mes,
DATENAME(month,fecha+id) Mes_Castellano,
DATENAME(month,Fecha + id ) +' '+ cast(YEAR(Fecha + id ) as varchar(4)) Mes_año_Castellano,
year(Fecha + id )*100+month(Fecha + id ) MesKey,
DATEPART(DW,Fecha + id ) Semana,
year(Fecha + id )*100 + DATEPART(DW,Fecha + id ) SemanaKey,
cast(DAY(Fecha + id ) as varchar(10)) + ' de ' + DATENAME(MM,Fecha + id ) + ' de ' + cast(YEAR(Fecha + id ) as varchar(4)) Fecha_Castellano,
case when datepart(weekday,Fecha + id )>5 then 'Fin de semana' else 'Semana' end TipoDia,
datepart(dayofyear,Fecha + id ) DiaDelAño,
DATEPART(DW,Fecha + id ) DiaDeLaSemana,
DATEPART(dd,Fecha + id ) DiaDelMes,
DATENAME(dw,Fecha + id ) Dia_castellano</code>
 
from (select CAST('19991231' as datetime) fecha ) tabla
cross join nums;

Con este primer insert tendremos rellena la mayor parte de la tabla y los descriptores en castellano, sin embargo, aún no tenemos rellenados los campos en inglés y francés que pretendemos tener completos. Veamos el código para actualizar la parte en inglés

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

set language english;
 
update Dimensiones.Tiempo 
set Fecha_Inglés=a.Fecha_Inglés,
   Dia_Inglés=a.Dia_ingles,
   Mes_año_Inglés=a.Mes_año_Inglés,
   Mes_Inglés=a.Mes_Inglés 
from 
 (  select 
  CAST(CONVERT(varchar(10),fecha  + id ,112) as int) FechaKey,
  DATENAME(month,fecha+id) Mes_Inglés,
  DATENAME(month,Fecha + id ) +' '+  cast(YEAR(Fecha + id ) as varchar(4)) Mes_año_Inglés,
  DATENAME(MM,Fecha + id ) ++ ' ' +cast(DAY(Fecha + id ) as varchar(10)) +  ', ' + cast(YEAR(Fecha + id ) as varchar(4)) Fecha_Inglés,
  DATENAME(dw,Fecha + id ) Dia_ingles
 
  from (select CAST('19991231' as datetime) fecha ) tabla 
      cross join nums ) a inner join Dimensiones.Tiempo on a.FechaKey=Dimensiones.Tiempo.FechaKey

En francés tendremos que hacer algo parecido, simplemente hay que elegir de nuevo el idioma.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

set language français;
update Dimensiones.Tiempo 
set Fecha_Francés=a.Fecha_Francés,
   Dia_Francés=a.Dia_ingles,
   Mes_año_Francés=a.Mes_año_Francés,
   Mes_Francés=a.Mes_Francés 
from 
 (  select 
  CAST(CONVERT(varchar(10),fecha  + id ,112) as int) FechaKey,
  DATENAME(month,fecha+id) Mes_Francés,
  DATENAME(month,Fecha + id ) +' '+  cast(YEAR(Fecha + id ) as varchar(4)) Mes_año_Francés,
  DATENAME(MM,Fecha + id ) ++ ' ' +cast(DAY(Fecha + id ) as varchar(10)) +  ', ' + cast(YEAR(Fecha + id ) as varchar(4)) Fecha_Francés,
  DATENAME(dw,Fecha + id ) Dia_ingles
 
  from (select CAST('19991231' as datetime) fecha ) tabla 
      cross join nums ) a inner join Dimensiones.Tiempo on a.FechaKey=Dimensiones.Tiempo.FechaKey

Espero que os sirva el código.

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.

Para distintas funcionalidades (os pondré algún ejemplo en los siguientes dias que vienen del gran Itzik )  es muy interesante tener una tabla con números del 1 al n, se pueden usar para convertir parámetros separados por comas en valores, se pueden usar para simular joins y duplicar filas, bueno, tienen un gran número de utilidades y funcionalidades.

Lo que os voy a contar hoy es como hacerlo sin recurrir a los cursores

Lo típico es utilizar una sentencia del tipo while para rellenar esos valores. Veamos un ejemplo

declare @num int

 set @num=0

  begin

       insert into @t values (@num)

      set @num=@num+1

end

select * from @t

Esto no deja de ser un cursor, y ya sabeis que soy de la opinión de que el único cursor bueno es el cursor muerto y me considero miembro activo de “la brigada anticursores”.   Antes de SQL 2005, no podía sin embargo matar este cursor excepto usando triquiñuelas que nada me gustaban y que probablemente eran peor que el propio cursor , sin embargo con las CTE’s son sencillos de eliminar. Las CTE’s permiten recursividad, es decir yo puedo basar el resultado de un query en el resultado del anterior, esta va a ser la idea, primero declararemos la cte con la cláusula with, luego pondremos la cláusula de inicio que será un siemple SELECT 0 as id, después usaremos la CTE para incrementar de 1 en uno y luego  pondremos la cláusula de salida de la recursividad ( en el where ), el resultado es el siguiente :

 

La única curiosidad que nos queda por resolver es la última cláusula (option maxrecursion) eso es simplemente porque el limite de llamadas recursivas en SQL Server 2005 y 2008 está en 100, (en SQL 2000 estaba en 32), y hay que prevenirse de esa limitación para no obtener un error. Para ello usamos ese Query hint. En el proximo artículo lo usaremos con fechas que también funciona y bastante bien, y veremos un par de lugares en el que nos puede resultar útil.

Os voy a felicitar la navidad, con lo que hacemos los techies, con un artículo, espero que resulte interesante para vosotros

10 de Septiembre de 2001

Introducción

    Este ejemplo pretende dar mi opinión sobre como tratar una gestión de acumulados de almacén desde Sql-Server y Visual-Basic. El ejemplo se basa en una pregunta al grupo de noticias de Sql-Server en Castellano y pretende resolver este punto en base a , de un lado tener acumulados de compras y ventas mensuales ágiles, y de otra parte tener estos mismos datos desde un punto de vista anual con la misma agilidad.
    Es de suponer que tambien se necesite tener un control del almacén desde un punto de vista global -¿Cuantos bolígrafos tengo?-, pero eso puede conserguirse bien desde estás mismas tablas, usando sentencias de agrupamiento por el criterio que se necesite, o bien incrementando la funcionalidad de los triggers que aquí se proponen para que acumulen también esa información. La decisión dependerá siempre del rendimiento que se espere del sistema…..
    Notad que no se ha tenido en cuenta multialmacén para el ejemplo y que se ha desnormalizado la relación Albaran-Lineas al objeto de tener más accesible la fecha ya que de otra forma complicaría en exceso el código y los detalles a tener en cuenta.     Yo creo que una desnormalización no es algo intrínsecamente negativo, existen casos como este que justifican la pérdida de espacio, por la agilidad y sencillez en el desarrollo. Tambien en este caso hay motivos de rendimiento evidentes que justifican esta desnormalización

La parte de Visual-Basic
    Una imagen vale más que mil palabras así que aquí tienes la pantallita que es lo que hace, ahora bien, si quieres descargarte todo el código haz click aquí

 

La parte de Sql-Server
    El código relativo al sql-server se divide a su vez en dos partes diferentes, la primera simplemente genera la estructura de tabla que soporta nuestro ejemplo, la segunda, contiene los dos triggers, realmente casi idénticos que aportarán esos acumulados mensuales y anuales. No he tenido en cuenta todos los colores que ofrece el sql-Query-analizer ya que es un pelín pesadito hacer esto en web….
El código de creación de tablas

if not object_id(‘Stocks’) is null
drop table stocks
go
if not object_id(‘StocksAño’) is null
drop table stocksaño
go
if not object_id(‘LineasAlbaranCompra’) is null
drop table lineasAlbaranCompra
go
if not object_id(‘LineasAlbaranVenta’) is null
drop table lineasAlbaranVenta
go

CREATE TABLE Stocks (año int not null, mes int not null,articulo int not null,
cantidadComprada float, ValorComprado float,
CantidadVendida float , ValorVendido float,
stock as isnull(CantidadComprada,0)-isnull(CantidadVendida,0))
go

create table StocksAño(año int not null, articulo int not null ,
cantidadComprada float, ValorComprado float,
CantidadVendida float , ValorVendido float,
stock as isnull(CantidadComprada,0)-isnull(CantidadVendida,0))
go

alter table Stocks add constraint pk_Compra primary key (año,mes,articulo)
go
alter table Stocksaño add constraint pk_compraaño primary key (año,articulo)
go

Create table LineasAlbaranCompra(id int not null, linea int not null,
Articulo int,Fecha datetime, Cantidad Float,
Precio Float )
go
alter table LineasAlbaranCompra add constraint pk_lac primary key (id,linea)

go
Create table LineasAlbaranVenta(id int not null, linea int not null,
Articulo int,Fecha datetime, Cantidad Float,
Precio Float )
go
alter table LineasAlbaranVenta add constraint pk_lav primary key (id,linea)
go

El código del trigger
    Observad que tiene suficientes comentarios para entender perfectamente lo que hace, por otra parte en el fichero zip teneis el código completo, aquí solo pondré uno de los dos triggers, ya que son practicamente igualitos.

create trigger trg_LineaAlbaranCompra on LineasAlbaranCompra
for insert,delete,update
as
begin

– ********************************************************************** –
– PRIMERO CON LAS INSERCIONES …………………………………. –
– ********************************************************************** –
– Primero intentamos actualizar las Stocks
– si no existe la clave primaria en el registro
– El update no hará nada, si si lo tiene actualizará los valores como yo le diga…

– en un update no se puede usar la clausula group by…..
– por lo que nos vemos obligados ha agruparlos antes…..
– Si estuviesemos en ss-2000 quizá podríamos usar variables
– en lugar de tablas temporales, pero las pruebas están hechas
– contra sql-server 7.0

select year(fecha) año ,month(fecha) mes, articulo,Sum(Cantidad) Cantidad ,sum(cantidad*Precio) Total
into #i
from inserted
group by year(inserted.fecha),month(inserted.fecha),articulo
– y ahora a actualizar…

– Primero a las Stocks mensuales ….
update Stocks Set CantidadComprada=isnull(CantidadComprada,0)+Cantidad,
ValorComprado = isNull(Valorcomprado,0)+Total
from #i Nuevas inner join Stocks
on Nuevas.Articulo=Stocks.articulo and
Nuevas.año = Stocks.año and
Nuevas.mes = Stocks.mes
– y después a las anuales…, el update es el mismo….
update StocksAño Set CantidadComprada=isnull(CantidadComprada,0)+Cantidad,
ValorComprado = isNull(Valorcomprado,0)+Total
from #i Nuevas inner join StocksAño
on Nuevas.Articulo=Stocksaño.articulo and
Nuevas.año = Stocksaño.año

– Algunas tendrán que ser insertadas ya que no existe el regitro
– de ese articulo en este año-mes, Luego haremos un insert into…
– Primero mensuales ….
insert into Stocks (año,mes,articulo,CantidadComprada,ValorComprado)
select nuevas.año,nuevas.mes,Nuevas.articulo,Cantidad,Total
from #i Nuevas left join Stocks
on Nuevas.Articulo=Stocks.articulo and
Nuevas.año = Stocks.año and
Nuevas.mes = Stocks.mes
where Stocks.articulo is null
РDespu̩s anuales ..
insert into StocksAño (año,articulo,CantidadComprada,ValorComprado)
select nuevas.año,Nuevas.articulo,Cantidad,Total
from #i Nuevas left join StocksAño
on Nuevas.Articulo=StocksAño.articulo and
Nuevas.año = StocksAño.año
where StocksAño.articulo is null

– lo que hago en esta insert es tener en cuenta que un not in que es lo
Рque te pide el cuerpo (Insertar la que no est̩n ya), es un coste muy
– alto, luego se hace un join externo que devuelve nulos con mucho menor
– coste y se exige que el campo relacionado sea nulo, esto aunque es –
– básico conviene no olvidarlo….

– ********************************************************************** –
– AHORA CON LOS BORRADOS …………………………………. –
– ********************************************************************** –
– pasa lo mismo, el update no soporta group by por lo que lo tenemos que
– hacer un poquito antes….

select year(fecha) año ,month(fecha) mes, articulo,Sum(Cantidad) Cantidad ,sum(cantidad*Precio) Total
into #d
from deleted
group by year(fecha),month(fecha),articulo

– Primero mensuales …
update Stocks Set CantidadComprada=isnull(CantidadComprada,0)- Cantidad,
ValorComprado = isNull(Valorcomprado,0)- Total
from #d Viejos inner join Stocks
on Viejos.Articulo=Stocks.articulo and
Viejos.año = Stocks.año and
viejos.mes = Stocks.mes
– Luego Anuales …
update StocksAño Set CantidadComprada=isnull(CantidadComprada,0)- Cantidad,
ValorComprado = isNull(Valorcomprado,0)- Total
from #d Viejos inner join StocksAño
on Viejos.Articulo=StocksAño.articulo and
Viejos.año = StocksAño.año

– Supongo, aunque luego la experiencia te dice que no siempre es así que
– para borrar algo primero debe haberse insertado y si se ha insertado
– ya existe, luego no hace falta insertar el – lo que sea del campo Stocks
– Digo que la experiencia dice otra cosa ya que si un usuario toca a mano
– la tabla Stocks,….. cualquier cosa podría suceder y deberiamos tenerla
– contemplada…

end

go

Después de meses sin funcionar, gracias a los spammers,  que como todos sabemos tanto bien hacen a la red y a su contenido (Para ganar qué?… me pregunto yo) y Gracias a mi amigo Antonio Salinas, acabo de instalar la utilidad para hacer de Portalsql lo que seguramente quise siempre.. A ver si empiezo a recuperar todo el contenido y pronto estamos 100% operativos.