Update Incident Expiration (Work in Progress)

Overview

⚠️ WARNING: This script is marked as WORK IN PROGRESS and should NOT be used in production environments.

This utility script is designed to update incident expiration records by matching them with their corresponding original incidents and updating point values. It identifies expiration records (RULESOURCE = '1') that correspond to actual incidents and ensures the expiration points match the original incident points. The script is currently in development and includes safety measures to prevent accidental execution.

Parameters

This script has no input parameters - it analyzes all incident records based on hardcoded criteria:

• Target Records: Incidents with RULESOURCE = '1' (expiration records)
• Matching Criteria: ORIGRULESOURCE, EVENTDATE, and date range validation
• Time Window: 12-month expiration period from original incident

Data Components

The script performs several key operations:

1. Expiration Identification - Finds incident expiration records
2. Original Incident Matching - Links expirations to their source incidents
3. Date Validation - Ensures expiration dates fall within valid ranges
4. Point Synchronization - Updates expiration points to match original incidents
5. Results Reporting - Shows which records would be updated

Output Format

The script returns information about records that would be updated:

Technical Implementation

The script uses:

• Temporary table creation for tracking update candidates
• Complex JOIN conditions to match expirations with original incidents
• Date range validation with DATEADD function
• Commented UPDATE logic for safety during development
• EXISTS subqueries for efficient record matching

Matching Logic

1. Rule Source Filtering: Only processes expiration records (RULESOURCE = '1')
2. Original Source Matching: Links via ORIGRULESOURCE field
3. Date Correlation: Matches EVENTDATE with EXPIRESON from original incident
4. Time Window Validation: Ensures expiration is within 12 months of original
5. Chronological Validation: Expiration date must be after original incident date

Safety Features

• Development Status Warning: Clear marking as work in progress
• Commented UPDATE Statement: Prevents accidental data modification
• Preview Mode: Shows what would be updated without making changes
• Temporary Table Cleanup: Proper resource management

Notes

• ⚠️ DEVELOPMENT STATUS: Script is incomplete and not ready for production
• Safety First: UPDATE logic is commented out to prevent accidental execution
• Preview Mode: Currently only shows what would be updated
• 12-Month Window: Enforces standard incident expiration timeframe
• Point Synchronization: Ensures expiration points match original incident points
• Data Integrity: Maintains proper incident-to-expiration relationships
• Temporary Resources: Uses temporary tables for safe processing
• DO NOT USE IN PRODUCTION until development is complete
• Test thoroughly on development/test databases only
• Verify the matching logic meets business requirements
• Review point calculation rules before enabling updates
• Consider adding additional validation and error handling
• Document the completion criteria before marking as production-ready
• Ensure proper backup procedures before any future production use

T-SQL

-- =========================================
-- STATUS: WORK IN PROGRESS / INCOMPLETE
-- DO NOT USE IN PRODUCTION
-- =========================================

--Select * from INCIDENTS where FILEKEY=497

-- Identify rows to update
SELECT E.UNIQUEID AS Updated_UniqueID, E.FILEKEY AS Updated_FileKey, E.POINTS AS Updated_Points
INTO #UpdatedRows
FROM Incidents AS E
WHERE E.RULESOURCE = '1' -- Update only expirations
    AND EXISTS (
    SELECT 1
    FROM Incidents AS I
    WHERE I.RULESOURCE <> '1' -- Ensure that it's not an incident
        AND E.ORIGRULESOURCE = I.ORIGRULESOURCE -- Match ORIGRULESOURCE
        AND E.EVENTDATE = I.EXPIRESON -- Match EXPIRESON with EVENTDATE
        AND E.EVENTDATE > I.EVENTDATE
        AND E.EVENTDATE <= DATEADD(month, 12, I.EVENTDATE)
);
/*
-- Update the selected rows
UPDATE Incidents
SET POINTS = (
    SELECT I.POINTS
    FROM Incidents AS I
    WHERE I.RULESOURCE <> '1'
    AND Incidents.ORIGRULESOURCE = I.ORIGRULESOURCE
    AND Incidents.EVENTDATE > I.EVENTDATE
    AND Incidents.EVENTDATE <= DATEADD(month, 12, I.EVENTDATE)
)
WHERE UNIQUEID IN (SELECT Updated_UniqueID FROM #UpdatedRows);
*/
-- Select the updated rows
SELECT *
FROM #UpdatedRows;

-- Clean up the temporary table
DROP TABLE #UpdatedRows;

Content Inventory

• Doc File: content/docs/utilities/update_incident_expiratation_wip.mdx
• SQL Script: SQL/utilities/Update_incident_expiratation_WIP.sql