Imagicle Call Analytics
Imagicle Call Analytics
Breadcrumbs

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.

  1. Install Microsoft SQL Management Studio, (Setup and Documentation are available free on Microsoft web site)

  2. Make a security backup of the database (it preservers from accidental data loss)

  3. Use the queries below to delete duplicated calls

Applies to: Imagicle UCX Suite start from Spring 2020.3.1

Query to delete duplicated calls in a specific date range

Replace in the query below:
- SXXX with your site code (i.e. SIT1)
- StartDate and EndDate with the first and last dates of the date interval to be considered, in YYYY-MM-DD format (ex. “2026-04-23”).

DECLARE @StartDate  date        = 'YYYY-MM-DD';
DECLARE @EndDate    date        = 'YYYY-MM-DD';
DECLARE @CodiceSito varchar(50) = 'SXXX';

;WITH ChiamateDuplicate AS
(
    SELECT
        c.Id,
        ROW_NUMBER() OVER
        (
            PARTITION BY
                c.UtcStartDateTime,
                c.DurationInSeconds,
                c.SiteId,
                c.LocalPartyId,
                c.RemotePartyId,
                c.IsExternal,
                c.IsOutgoing,
                c.PbxCallId
            ORDER BY c.Id
        ) AS rn
    FROM dbo.BibCalls AS c
    INNER JOIN dbo.BibSites AS s
        ON s.Id = c.SiteId
    WHERE 
    s.Code = @CodiceSito AND 
    c.UtcStartDateTime >= @StartDate AND 
    c.UtcStartDateTime < DATEADD(day, 1, @EndDate)
)
DELETE c
FROM dbo.BibCalls AS c
INNER JOIN ChiamateDuplicate AS d
    ON d.Id = c.Id
WHERE d.rn > 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.

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

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'

Last updated: