USE ION_Data
DECLARE @EventsToDeleteCount INT
DECLARE @AlarmsToDeleteCount INT
-- Declare the table Events to Remove
CREATE TABLE #EventIDsToRemove (ID INT PRIMARY KEY NOT NULL)
-- Fill the table of relevant Events to remove
INSERT INTO #EventIDsToRemove
    SELECT ID FROM vEventLog2
    WHERE
        -- *** START CUSTOM CRITERIA HERE
        --
        -- Note.  It is recommended you test your criteria first using the following query to ensure you get the exact match you want:
        -- SELECT * FROM vEventLog2 WHERE <your criteria to test>
        --
        -- *** END CUSTOM CRITERIA HERE
         
SELECT @EventsToDeleteCount = COUNT(*) FROM #EventIDsToRemove
PRINT 'Deleting ' + CONVERT(NVARCHAR(MAX), @EventsToDeleteCount) + ' Events.'
 
-- Declare the table Alarms to Remove
CREATE TABLE #AlarmsToRemove (ID INT PRIMARY KEY NOT NULL)
INSERT INTO #AlarmsToRemove
    SELECT DISTINCT AlarmID FROM AlarmEvent WHERE EventID IN (SELECT ID FROM #EventIDsToRemove)
SELECT @AlarmsToDeleteCount = COUNT(*) FROM #AlarmsToRemove
PRINT 'Deleting ' + CONVERT(NVARCHAR(MAX), @EventsToDeleteCount) + ' Alarms.'
 
-- Declare the table AlarmDefinitions to Update
CREATE TABLE #AlarmDefinitionsToUpdate(ID INT PRIMARY KEY NOT NULL)
INSERT INTO #AlarmDefinitionsToUpdate
    SELECT DISTINCT AlarmDefinitionID FROM Alarm WHERE ID IN (SELECT ID FROM #AlarmsToRemove)
 
-- Delete AlarmStates for the affected Alarms
DELETE FROM AlarmStates WHERE AlarmID IN (SELECT ID FROM #AlarmsToRemove)
 
-- Delete Incident -> Alarm association for the affected Alarms
DELETE FROM IncidentAlarm WHERE AlarmID IN (SELECT ID FROM #AlarmsToRemove)
 
-- Delete EventAcknowledgement association for the affected Events
DELETE FROM EventAcknowledgement WHERE EventLogID IN (SELECT ID FROM #EventIDsToRemove)
 
-- Delete Alarm -> Event association for the affected Alarms
DELETE FROM AlarmEvent WHERE AlarmID IN (SELECT ID FROM #AlarmsToRemove)
 
-- Delete Alarms
WHILE EXISTS (SELECT TOP 1 ID FROM #AlarmsToRemove)
BEGIN
    DELETE FROM Alarm WHERE ID IN (SELECT TOP 10000 ID FROM #AlarmsToRemove ORDER BY ID)
    DELETE FROM #AlarmsToRemove WHERE ID IN (SELECT TOP 10000 ID FROM #AlarmsToRemove ORDER BY ID)
END
 
-- Update AlarmDefinition
UPDATE AlarmDefinition
    SET
        AlarmDefinition.Occurrences = (SELECT COUNT(*) FROM dbo.Alarm WHERE AlarmDefinitionID = AlarmDefinition.ID),
        AlarmDefinition.Unacknowledged = (SELECT COUNT(*) FROM dbo.Alarm WHERE AlarmDefinitionID = AlarmDefinition.ID AND Alarm.AcknowledgementID IS NULL),
        AlarmDefinition.IsActive = (SELECT TOP 1 (IsActive+0) FROM dbo.Alarm WHERE AlarmDefinitionID = AlarmDefinition.ID ORDER BY StartTimestampUTC DESC),
        AlarmDefinition.FirstOccurrenceUTC = (SELECT MIN(StartTimestampUTC) FROM Alarm WHERE AlarmDefinitionID = AlarmDefinition.ID),
        AlarmDefinition.LastOccurrenceUTC = (SELECT MAX(StartTimestampUTC) FROM Alarm WHERE AlarmDefinitionID = AlarmDefinition.ID)
    FROM #AlarmDefinitionsToUpdate updatedAlarmDefs
    WHERE
        AlarmDefinition.ID = updatedAlarmDefs.ID
 
-- Delete Events
WHILE EXISTS (SELECT TOP 1 ID FROM #EventIDsToRemove)
BEGIN
    DELETE FROM EventLog2 WHERE ID IN (SELECT TOP 10000 ID FROM #EventIDsToRemove ORDER BY ID)
    DELETE FROM #EventIDsToRemove WHERE ID IN (SELECT TOP 10000 ID FROM #EventIDsToRemove ORDER BY ID)
END
 
DROP TABLE #AlarmDefinitionsToUpdate
DROP TABLE #AlarmsToRemove
DROP TABLE #EventIDsToRemove