En algunas ocasiones tenemos la necesidad de armar consultas en T-SQL para retornar acumulados.
Imaginemos el siguiente resultado

Lo que nosotros tenemos es una tabla la cual contiene TRANID, PRODUCTID,TRANSACTIONDATE y QUANTITY y necesitamos calcular la columna total que como podemos observar hace un acumulado con la particularidad de poner en 0 de nuevo cada vez que cambia el código de producto.
En este tutorial te voy a mostrar las alternativas para poder resolver esto y cual es la mas eficiente de todas
Para poder ejecutar el código de ejemplo he utilizado la base de datos de ejemplos Adventureworks
Código de ejemplo utilizado en el video
USE ADVENTUREWORKS2019
GO
-- PREPARAR DATOS DE PRUEBA
DROP TABLE IF EXISTS MOVIMIENTOS
SELECT IDENTITY(INT, 1,1) AS TRANID
,[PRODUCTID]
,[TRANSACTIONDATE]
,[QUANTITY] * FLOOR(10 * RAND(CONVERT(VARBINARY, NEWID()))) AS QUANTITY
INTO MOVIMIENTOS
FROM [ADVENTUREWORKS2019].[PRODUCTION].[TRANSACTIONHISTORY]
CROSS JOIN (SELECT TOP 10 * FROM SYS.COLUMNS) AS C
ORDER BY PRODUCTID,TRANID
---------------------------------------
------ USANDO OVER
----------------------------------------
SELECT [TRANID]
,[PRODUCTID]
,[TRANSACTIONDATE]
,[QUANTITY]
, SUM(QUANTITY)
OVER(PARTITION BY PRODUCTID ORDER BY TRANID) AS TOTAL
FROM DBO.MOVIMIENTOS
ORDER BY PRODUCTID,TRANID
--------------------------------------
-- USANDO CURSORES Y TABLAS TEMPORALES
---------------------------------------
DECLARE @RESULT AS TABLE (TRANID INT,
PRODUCTID INT,
TRANSACTIONDATE DATETIME,
QUANTITY INT,
TOTAL INT
)
DECLARE
@TRANID AS INT,
@PREVPRODUCTID AS INT,
@PRODUCTID AS INT,
@TRANSACTIONDATE AS DATETIME,
@QUANTITY AS INT,
@TOTAL AS INT;
DECLARE C CURSOR FAST_FORWARD FOR
SELECT TRANID,PRODUCTID,TRANSACTIONDATE,QUANTITY
FROM DBO.MOVIMIENTOS
ORDER BY PRODUCTID,TRANID;
OPEN C
FETCH NEXT FROM C INTO @TRANID, @PRODUCTID, @TRANSACTIONDATE,@QUANTITY
SELECT @PREVPRODUCTID = @PRODUCTID , @TOTAL = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PRODUCTID <> @PREVPRODUCTID
SELECT @PREVPRODUCTID = @PRODUCTID , @TOTAL = 0;
;
SET @TOTAL = @TOTAL + @QUANTITY;
INSERT INTO @RESULT
VALUES(@TRANID, @PRODUCTID, @TRANSACTIONDATE , @QUANTITY,@TOTAL);
FETCH NEXT FROM C INTO @TRANID, @PRODUCTID, @TRANSACTIONDATE,@QUANTITY;
END
CLOSE C;
DEALLOCATE C;
SELECT * FROM @RESULT
ORDER BY PRODUCTID,TRANID
------------------------------------
-- USANDO SUBQUERY
-------------------------------------
SELECT T1.PRODUCTID , T1.TRANID, T1.QUANTITY ,
SUM(T2.QUANTITY ) AS TOTAL
FROM DBO.MOVIMIENTOS AS T1
JOIN DBO.MOVIMIENTOS AS T2
ON T2.PRODUCTID = T1.PRODUCTID
AND T2.TRANID <= T1.TRANID
GROUP BY T1.PRODUCTID , T1.TRANID, T1.QUANTITY
ORDER BY 1,2;
0 Comments
You must log in to post a comment.