Knowlege base

How to delete calls between 2 dates and re-import it from the Storico file

Article ID: 373
Last updated: 23 Jul, 2020

Applies to: 

Blue's Enterprise 4

Description:

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

Solution: Application Suite starting from 2020.3.1

1. Stop Blue's Service Host in Billing|Administrative pages|Service Manager

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! */

-- 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-01 00:00:00.000'

-- 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 [BibCalls] 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 [BibCalls] WHERE [StartDate] >= @startDate AND [StartDate] < @endDate
    RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT

    BEGIN TRANSACTION
    DELETE FROM [BibCalls] WHERE [StartDate] >= @startDate AND [StartDate] < @endDate
    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 \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro, from file StoricoSITE.txt, select all the lines you need to reimport and hit CTRL+C on the keyboard

5. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro open bluesrecsite.txt and use CTRL+V to paste in the previously copied strings


6. Start Blue's Service Host in Billing|Administrative pages|Service Manager and wait until miner has finished importing all calls

Solution: Application Suite previous 2020.3.1 

1. Stop Blue's Service Host in Billing|Administrative pages|Service Manager

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

3. Run "New Query" and copy the following query:


/* 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

( change the dates of the above query according to the period you wish to delete)

4. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro, from file StoricoSITE.txt, select all the lines you need to reimport and hit CTRL+C on the keyboard

5. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro open bluesrecsite.txt and use CTRL+V to paste in the previously copied strings

6. Start Blue's Service Host in Billing|Administrative pages|Service Manager and wait until miner has finished importing all calls

Article ID: 373
Last updated: 23 Jul, 2020
Revision: 9
Views: 111
Print Export to PDF Subscribe Share
This article was:  
Prev   Next
How to modify attachment size limit on scheduled reports     Show calls currency using 3 English letters