Cacher is the code snippet organizer for pro developers

WITH tblwDuplicados(numeroCuentaCliente,num) AS (
	SELECT 
		numeroCuentaCliente,
		ROW_NUMBER() OVER(partition by numeroCuentaCliente ORDER BY fchCreado DESC)
	FROM tblCuentaDireccion
)	
DELETE FROM tblwDuplicados
WHERE num > 1;


WITH tmpResumenGestiones(creadoPor,num) as (
    SELECT
        creadoPor,
        ROW_NUMBER() OVER (PARTITION BY creadoPor ORDER BY fecha) as num
    FROM tblResumenGestiones
    WHERE fecha = '28/05/2015'
)
DELETE FROM tmpResumenGestiones WHERE num = 1

TRUNCATE TABLE tmpCuentas

INSERT INTO tmpCuentas
SELECT DISTINCT(numeroCuentaCliente)
FROM tblCarteraCliente cc (NOLOCK)
WHERE fechaAsignacion > '01/01/2021'
	AND idCategoria IN (1,2,5,8)
	AND territorial = 'R4'

--CAMBIAR EL NOMBRE DE LA TABLA TEMPORAL

SELECT 
	cc.numeroCuentaCliente,
	cc.nombreCompleto,
	cat.descripcionCategoria,
	'0000000000' as numeroTelefonico,
	cd.calle,
	cd.colonia,
	cd.cp,
	cd.ciudad,
	cd.estado,
	cc.fechaAsignacion
INTO tmpExtraccion12042021
FROM tblCarteraCliente cc (NOLOCK) INNER JOIN tmpCuentas tmp (NOLOCK)
	on cc.numeroCuentaCliente = tmp.numeroCuentaCliente INNER JOIN tblCategoria cat (NOLOCK)
	on cc.idCategoria = cat.idCategoria INNER JOIN tblCuentaDireccion cd (NOLOCK)
	ON cc.numeroCuentaCliente = cd.numeroCuentaCliente
		AND cc.idCategoria = cd.idCategoria
WHERE cd.estado = 'NUEVO LEÓN'


SELECT cc.numeroCuentaCliente,ct.numeroTelefonico
INTO tmpExtraccionTel12042021
FROM tblCarteraCliente cc (NOLOCK) INNER JOIN tmpCuentas tmp (NOLOCK)
	on cc.numeroCuentaCliente = tmp.numeroCuentaCliente INNER JOIN tblClienteMaestroCliente cmc (NOLOCK)
	on cc.numeroCuentaCliente = cmc.numeroCuentaCliente  INNER JOIN tblClienteTelefono ct (NOLOCK)
	on cmc.idMestroEntidad = ct.idMaestroEntidad
		AND cc.idCategoria = ct.idCategoria
		AND ct.clasificacion = 1 INNER JOIN tblTelefono tel (NOLOCK)
	on ct.numeroTelefonico = tel.numeroTelefonico 
		AND tel.idTipoTelefono IN (2,3)


WITH tblwDuplicados(numeroCuentaCliente,num) AS (
	SELECT 
		numeroCuentaCliente,
		ROW_NUMBER() OVER(partition by numeroCuentaCliente ORDER BY numeroTelefonico DESC)
	FROM tmpExtraccionTel12042021
)	
DELETE FROM tblwDuplicados
WHERE num > 1;


UPDATE tmpExtraccion12042021
	SET numeroTelefonico = tel.numeroTelefonico
FROM tmpExtraccion12042021 dir INNER JOIN tmpExtracciontel12042021 tel
	ON dir.numeroCuentaCliente = tel.numeroCuentaCliente



SELECT * FROM tmpExtraccion12042021;
SELECT * FROM tmpExtraccionTel12042021;



DROP TABLE tmpExtraccion12042021;
DROP TABLE tmpExtraccionTel12042021;