Fila a fila sin cursores: columna agregado

Tenemos una tabla en nuestra base de datos llamada “Movimientos”, que registra entradas y salidas de material de nuestro almacén. La siguiente consulta es el resultado de consultar los movimientos de “tornillos” en el mes de enero. Cómo se puede ver la tabla carece de un autonumérico, los registros no tienen porqué estar en orden cronológico y hay más productos mezclados. 

tornillos

500

01/01/2009

tornillos

-50

02/01/2009

tornillos

-200

04/01/2009

tornillos

-220

10/01/2009

tornillos

500

11/01/2009

tornillos

-100

20/01/2009

Si en este escenario nos pidiesen obtener la cantidad máxima de tornillos que llegó a haber en el almacén podrían ponernos en un pequeño apuro. La idea sería conseguir una tercera columna con la cantidad en stock y poder seleccionar el máximo. El problema de eso es que necesitamos de alguna manera recorrer la tabla fila a fila, pero claro, dicen por ahí que eso de los cursores es cosa mala….

tornillos

500

01/01/2009

500

tornillos

-50

02/01/2009

450

tornillos

-200

04/01/2009

250

tornillos

-220

10/01/2009

30

tornillos

500

11/01/2009

530

tornillos

-100

20/01/2009

430

He aquí un método alternativo a la utilización de los cursores para obtener la columna “agregado” y poder seleccionar el máximo stock, que tiene lugar el 11 de enero con un valor de 530 tornillos.

1º) Creamos una tabla temporal con los registros que nos interesan, ordenándolos por orden cronológico

2º) Recorremos un bucle que analice la primera fila y la eliminamos, de esta forma podemos recorrer la tabla completa.

Veámoslo en el ejemplo:

–Creamos la tabla del ejemplo. Para simplificar introducimos solo los

–datos que utilizaremos, pero esto no afecta al ejemplo

declare @movimientos as table

(Producto nvarchar(10), Cantidad int, Fecha date)

insert into @movimientos values (‘tornillos’,500,‘20090101’)

insert into @movimientos values (‘tornillos’,-200,‘20090104’)

insert into @movimientos values (‘tornillos’,-50,‘20090102’)

insert into @movimientos values (‘tornillos’,-220,‘20090110’)

insert into @movimientos values (‘tornillos’,500,‘20090111’)

insert into @movimientos values (‘tornillos’,-100,‘20090120’)

 

–Definimos una tabla temporal, con la misma estructura que la original

declare @tmp as table

(Producto nvarchar(10), Cantidad int, Fecha date)

 

–Introducimos aquí los datos que nos interesen, ordenados convenientemente

insert into @tmp

      select * from @movimientos

      where Producto=‘tornillos’

      order by fecha asc

 

–Variables auxiliares que utilizaremos

declare @Acc int,@Max int

set @Acc=0 –Valor acumulado hasta la fila actual

set @Max=0 –Valor máximo hasta la fila actual

 

–Esta instrucción elimina de la ventana de mensajes el recuento de filas afectadas

set nocount on

 

–Mientras exista alguna fila en @tmp recorro el bucle

While(Exists(select * from @tmp))

      BEGIN

            –Sumamos la 1ªfila al acumulado

            select top(1) @Acc=@Acc+Cantidad from @tmp;

            –Eliminamos la 1ª fila; ya está computada

            delete top(1) from @tmp;

            –Comprobamos si el acumulado es máximo

            if(@Acc>@Max)

                  set @Max=@Acc;

           

            print @Acc;

      END

     

print »

print ‘Máximo alcanzado:’

print @Max

 

Lo que nos devuelve por pantalla la columna de acumulados y el máximo stock que llegó a haber en el almacén:

500

450

250

30

530

430

 

Máximo alcanzado:

530

 

La pega de este método son la cantidad de deletes que utiliza, así que en el siguiente post tocará un método alternativo (sin cursores, of course) más eficaz.

 

3 comentarios sobre “Fila a fila sin cursores: columna agregado”

  1. Buen post, sólo una cosa sobre las tablas temporales por si puede aclararar, no es lo mismo tablas temporales que variables de tipo table, aunque se puedan usar de manera similar:

    Tablas temporales:
    http://technet.microsoft.com/es-es/library/ms177399.aspx

    Variables tipo table:
    http://technet.microsoft.com/es-es/library/ms175010.aspx

    Por otro lado, la carga de la tabla temporal también se podría hacer aprovechando un SELECT INTO para la creación y la seleción a la vez:

    http://technet.microsoft.com/es-es/library/ms190750%28SQL.90%29.aspx

    Saludos!

  2. Gracias por la aclaración José: debería de cuidar un poco más el lenguaje por si alguien se confunde con las variables y las verdaderas tablas temporales, aunque en este ejemplo no sea relevante.

    Y sí, en efecto el select into nos ayudaría, aunque lo dejé así a propósito para el siguiente post, donde quiero añadir más columnas a la tabla.

Responder a jfortes Cancelar respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *