Delete old CCC records

[JKido]

Hi,

I would like to know if there is a proper procedure for deleting old records in the CCC?

There was a thread here: thread940-1677675 (

http://www.tek-tips.com/viewthread.cfm?qid=1677675)

but don't know if the query was answered properly.

Thanks.
JKido

 

[intrigrant]

You need a SQL script. I only have one for CCR maybe you can adapt it to CCC :
USE [AvayaSBCCRT]
GO

DECLARE @YEAR int;
DECLARE @MONTH int;
DECLARE @DAY int;

-- Specify the date in terms of year, month and day, of the oldest data you wish
-- to retain in the IPOCCR database. For example, the default values of 2009, 1 and 1
-- will cause any calls ending before 1st January 2009 to be deleted.
SET @YEAR = 2009
SET @MONTH = 1
SET @DAY = 1

DECLARE @oldestDate datetime
SELECT @oldestDate = min(DestroyDate) from tblCallList
IF @oldestDate IS NOT NULL
BEGIN
DECLARE @deleteToDate datetime
SET @deleteToDate = DATEADD(mm,(@YEAR-1900)* 12 + @MONTH - 1,0) + (@DAY-1)
PRINT 'All calls ending before '+CONVERT(nvarchar,@deleteToDate)+ ' will be deleted from the IPOCCR database'

DECLARE @callsToDelete int
SET @callsToDelete = (SELECT COUNT(CallListID) FROM tblCallList AS cl WHERE cl.DestroyDate < @deleteToDate)
PRINT 'The total number of calls deleted from the IPOCCR database will be '+CONVERT(nvarchar,@callsToDelete)

BEGIN TRAN
DELETE tblAgentActivity FROM tblAgentActivity AS aa
INNER JOIN tblCallList AS cl ON aa.CallListID = cl.CallListID
WHERE cl.DestroyDate < @deleteToDate
DELETE tblVoicemailSelection FROM tblVoicemailSelection AS vs
INNER JOIN tblCallList AS cl ON vs.CallListID = cl.CallListID
WHERE cl.DestroyDate < @deleteToDate

DELETE tblCallEnd FROM tblCallEnd AS ce
INNER JOIN tblCallList AS cl ON ce.CallListID = cl.CallListID
WHERE cl.DestroyDate < @deleteToDate
DELETE FROM tblCallList WHERE DestroyDate < @deleteToDate

DELETE FROM tblAlarmDetails WHERE ClearDate < @deleteToDate
DELETE tblAlarm FROM tblAlarm AS a
INNER JOIN tblAlarmDetails AS ads ON a.AlarmID = ads.AlarmID
WHERE ads.ClearDate < @deleteToDate

DELETE FROM tblAgentHGBridge WHERE DestroyDate < @deleteToDate

DELETE tblTrunkSupervisorBridge FROM tblTrunkSupervisorBridge AS tsb
INNER JOIN tblTrunkGroup AS tg ON tsb.TrunkGroupID = tg.TrunkGroupID
WHERE tg.DestroyDate < @deleteToDate
DELETE tblHGViewBridge FROM tblHGViewBridge AS hvb
INNER JOIN tblHuntGroup AS hg ON hvb.HGID = hg.HGID
WHERE hg.DestroyDate < @deleteToDate
DELETE tblHGSupervisorBridge FROM tblHGSupervisorBridge AS hsb
INNER JOIN tblHuntGroup AS hg ON hsb.HGID = hg.HGID
WHERE hg.DestroyDate < @deleteToDate

DELETE FROM tblLastStatReset WHERE ResetDate < @deleteToDate

DELETE FROM tblTrunkGroup WHERE DestroyDate < @deleteToDate
DELETE FROM tblVoicemailGroup WHERE DestroyDate < @deleteToDate
DELETE FROM tblHuntGroup WHERE DestroyDate < @deleteToDate
DELETE FROM tblSwitch WHERE DestroyDate < @deleteToDate

DELETE FROM tblDatabaseMonitorAlarms
COMMIT TRAN
END
ELSE
BEGIN
PRINT 'No data was found in the IPOCCR database to delete. Please proceed with installation.'
END


A simple mind delivers great solutions