Este es el contenido del curso online gratuito que estoy dando en conjunto con la gente de ITBA Computer Society en el cual en base a ejemplos prácticos enseñare el uso de TSQL para hacer consultas.
Para poder descargar el material del curso lo pueden hacer por medio del siguiente enlace de GitHUB
En este post dejare cada video de las 4 clases de 90 minutos cada una
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;
¿Cuántas veces hemos tenido la necesidad de eliminar registros duplicados de alguna tabla en SQL Server?
En este post y video te voy a mostrar como podes hacer esta tarea.
Crear tabla de datos
Mi primer paso será crear una tabla con datos duplicados para luego poderlos eliminar
USE TEMPDB
DROP TABLE IF EXISTS DBO.EMPLEADOS
CREATE TABLE DBO.EMPLEADOS
(ID INT IDENTITY PRIMARY KEY,
NOMBRE VARCHAR(255),
APELLIDO VARCHAR(255)
)
INSERT INTO DBO.EMPLEADOS (NOMBRE,APELLIDO)
VALUES ('MAXI','ACCOTTO')
GO 4
INSERT INTO DBO.EMPLEADOS (NOMBRE,APELLIDO)
VALUES ('GASTON','LOPEZ')
GO 2
INSERT INTO DBO.EMPLEADOS (NOMBRE,APELLIDO)
VALUES ('LEONARDO','ACCOTTO')
Buscar duplicados
Para buscar los registros duplicados usaremos una CTE con la función ROW_NUMBER()
-- BUSCAMOS DUPLICADOS
WITH C AS
(
SELECT ID,NOMBRE,APELLIDO,
ROW_NUMBER() OVER (PARTITION BY
NOMBRE,APELLIDO
ORDER BY ID) AS DUPLICADO
FROM DBO.EMPLEADOS
)
SELECT * FROM C
WHERE DUPLICADO > 1
Eliminando registros duplicados
Para eliminar los registros duplicados usaremos la misma CTE con la instrucción DELETE en lugar de SELECT
-- BORRAMOS REGISTROS DUPLICADOS
WITH C AS
(
SELECT ID,NOMBRE,APELLIDO,
ROW_NUMBER() OVER (PARTITION BY
NOMBRE,APELLIDO
ORDER BY ID) AS DUPLICADO
FROM DBO.EMPLEADOS
)
DELETE FROM C
WHERE DUPLICADO > 1