How to delete calls between 2 dates and re-import it from the Storico file (Blue's Enterprise Lite Edition)

Applies to:

Blue's Enterprise Lite Edition

Description:

How to delete calls between two dates from the database and reimport from storico

How-to:

1. Close Blues Recorder and Blues Miner (open Blues Enterprise Web page click Tools>Service Manager, stop the services)

2. open Microsoft SQL Manager Studio (make a backup copy of the database)

3. Run "New Query" and copy the following query:
( change the dates of the above query according to the period you wish to delete)

/* WARNING: BEFORE YOU RUN THIS SCRIPT TAKE A BACKUP OF THE DATABASE! */

-- Declare the date after which the calls will be canceled

-- The start date (included) of the deletion
DECLARE @startDate DATETIME = '2020-01-01 00:00:00.000'


-- The end date (excluded) of the deletion
DECLARE @endDate DATETIME = '2020-02-29 00:00:00.000'

-- Declare the site to be canceled 
DECLARE @sito VARCHAR(50)
set @sito = 'SITE'


-- Number of records that must be deleted in each chunk
DECLARE @recordsPerChunk INT = 1000

----- DO NOT EDIT BELOW THIS LINE -----
DECLARE @recordsNumber INT
DECLARE @startDateString VARCHAR(50) = CONVERT(VARCHAR(50), @startDate, 120)
DECLARE @endDateString VARCHAR(50) = CONVERT(VARCHAR(50), @endDate, 120)

SET ROWCOUNT @recordsPerChunk

RAISERROR('Deleting [ChiamateInterne] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
        SELECT @recordsNumber = COUNT(*) FROM [ChiamateInterne] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
        RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT
        
        BEGIN TRANSACTION
        DELETE FROM [ChiamateInterne] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
        COMMIT TRANSACTION
END


RAISERROR('Deleting [Chiamate] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
        SELECT @recordsNumber = COUNT(*) FROM [Chiamate] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
        RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT
        
        BEGIN TRANSACTION
        DELETE FROM [Chiamate] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
        COMMIT TRANSACTION
END

RAISERROR('Deleting [Scarti] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
    SELECT @recordsNumber = COUNT(*) FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT

    BEGIN TRANSACTION
    DELETE FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    COMMIT TRANSACTION
END

SET ROWCOUNT 0

4. in folder \\BluesEnterprise\BluesrecPro, from file StoricoSITE.txt, select all the lines you need to reimport and hit CTRL+C on the keyboard

5. in folder \\BluesEnterprise\BluesrecPro open bluesrecsite.txt and use CTRL+V to paste in the previously copied strings

6. Open Blues Enterprise Web page click Tools>Service Manager then Start the services

7. repeat the same procedure from the beginning for all sites (ie. StoricoSIT1 to BluesrecSIT1, StoricoSIT2 to BluesrecSIT2)