How to delete duplicated calls from the database

Description:

How to delete duplicated calls from the database.

This procedure MUST be run only by the SQL database server IT manager, it is NOT reversible. UCX Cloud Suite customers should contact Imagicle Support for more details.

Install Microsoft SQL Management Studio, (Setup and Documentation are available free on Microsoft web site)
Make a security backup of the database (it preservers from accidental data loss)
Use the queries below to delete duplicated calls

Applies to: Imagicle UCX Suite start from Spring 2020.3.1

Query to delete EXTERNAL duplicated calls

Replace SXXX with your site code (ie. SIT1) and 'yyyy-mm-dd 00:00:00' and 'yyyy-mm-dd 00:00:00' with your date range.

CODE

WITH temp AS (
    SELECT 
        c.[lPKId],
        ROW_NUMBER() OVER (
            PARTITION BY 
                c.[dData],
                c.[dOra],
                c.[dDurata],
                c.[sSito],
                c.[sDerivato],
                c.[sNumero]
            ORDER BY 
                c.[lPKId]
        ) AS rowNumber
    FROM [dbo].[Chiamate] c
    WHERE c.[sSito] = 'SXXX'
      AND c.[dData] BETWEEN 'yyyy-mm-dd 00:00:00' AND 'yyyy-mm-dd 00:00:00'
)
DELETE FROM [BibCalls]
WHERE [Id] IN (
    SELECT [lPKId] 
    FROM temp
    WHERE rowNumber > 1
);

Query to delete INTERNAL duplicated calls:

Replace 'yyyy-mm-dd 00:00:00' and 'yyyy-mm-dd 00:00:00' with your date range

CODE

WITH temp AS (
    SELECT 
        c.[lPKId],
        ROW_NUMBER() OVER (
            PARTITION BY 
                c.[dData],
                c.[dOra],
                c.[dDurata],
                c.[sSito],
                c.[sDerivato],
                c.[sNumero]
            ORDER BY 
                c.[lPKId]
        ) AS rowNumber
    FROM [dbo].[ChiamateInterne] c
    WHERE c.[sSito] = 'SITE'
      AND c.[dData] BETWEEN 'yyyy-mm-dd 00:00:00' AND 'yyyy-mm-dd 00:00:00'
)
DELETE FROM [BibCalls]
WHERE [Id] IN (
    SELECT [lPKId] 
    FROM temp
    WHERE rowNumber > 1
);

Applies to: Imagicle UCX Suite older then Spring 2020.3.1

Query to delete EXTERNAL duplicated calls:

Replace SXXX with your site code (ie. SIT1) and 'yyyy-mm-dd 00:00:00' and 'yyyy-mm-dd 00:00:00' with your date range.

CODE

delete from chiamate
WHERE lPKId not in
( SELECT Max(C2.lPKId) FROM CHIAMATE as C2
WHERE CHIAMATE.dData = C2.dData
AND CHIAMATE.dOra = C2.dOra
AND CHIAMATE.dDurata = C2.dDurata
and CHIAMATE.sSito = c2.sSito
and CHIAMATE.sNumero = c2.sNumero )
and sSito = 'SXXX'
and dData between 'yyyy-mm-dd 00:00:00' and 'yyyy-mm-dd 00:00:00'

Query to delete INTERNAL duplicated calls:

Replace 'yyyy-mm-dd 00:00:00' and 'yyyy-mm-dd 00:00:00' with your date range

CODE

delete from chiamateinterne
WHERE lPKId not in 
( SELECT Max(C2.lPKId) FROM CHIAMATEInterne as C2 
WHERE CHIAMATEinterne.dData = C2.dData 
AND CHIAMATEinterne.dOra = C2.dOra 
AND CHIAMATEinterne.dDurata = C2.dDurata 
and CHIAMATEinterne.sSito = c2.sSito
and CHIAMATEinterne.sDerivato = c2.sDerivato
and CHIAMATEinterne.sNumero = c2.sNumero ) 
and sSito = 'SITE'
and dData between 'yyyy-mm-dd 00:00:00' and 'yyyy-mm-dd 00:00:00'