Elimina Duplicados
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;