How to find orphaned records in the HEAT database

Version 1

    Details

    There may be instances where HEAT Call Records appear in Call Logging that do not have related records in one or more of the five HEAT core tables - CallLog, Subset, Journal, Detail, and Asgnmnt. This article provides SQL Scripts to identify orphaned records in the HEAT database.


    Resolution

     

    Below are SQL scripts for identifying orphaned records that exist in four of the core tables but does NOT exist in the CallLog table

    SELECT CallID FROM Asgnmnt WHERE (Asgnmnt.CallID) NOT IN (SELECT CallID FROM CallLog)

    SELECT CallID FROM Subset WHERE (Subset.CallID) NOT IN (SELECT CallID FROM CallLog)

    SELECT CallID FROM Detail WHERE (Detail.CallID) NOT IN (SELECT CallID FROM CallLog)

    SELECT CallID FROM Journal WHERE (Journal.CallID) NOT IN (SELECT CallID FROM CallLog)

    Additionally, you can identify which records exists in CallLog table that do not have records in the Subset table. For example, the below query finds CallLog records that do not have related Subset records -

    SELECT CallID FROM CallLog WHERE (CallLog.CallID) NOT IN (SELECT CallID FROM Subset)