SQL Slowly Changing Data and History Table Audit
Check that history is captured and queried correctly.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.
Audit history tracking. Tables that keep history (valid from and to dates, current flag), how they are updated, and queries that report on them: [HISTORY_TABLES]. Define the validity rule precisely: for any date, exactly one row per entity should be valid. Diagnostic queries in words with examples: entities with more than one current row; overlapping ranges (one row's start before another's end and the other's start before this end); gaps (each row's end compared with the next row's start); open-ended rows not marked current. Example (invented): a customer's address records valid 1 Jan to 30 Jun and 1 Jun to 31 Dec overlap for 30 days. Also check queries that join to history without a date condition, which duplicate rows and revenue, and updates that overwrite history. Return a table of findings, a verification query set, and fixes. Mark syntax [VERIFY]. Keep the fixes in a safe order, with a backup first, and say how to test each on a copy. Ask for the table definitions if I did not give them, because the validity rule cannot be checked without them.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.