Does anyone have a nice query for matching a merged account number to the active account that it ultimately leads to (obviously an entry in T_MERGED may just lead you to another account that has since been merged) without using a loop?
Update: unless there is a good way, maybe T_MERGED should have a column called "current_id" that updates all previous rows related to the merged id with the new id.
The standard merge procedure retains merged IDs in TX_CUST_KEYWORD with keyword_no = (S E L E C T keyword_no FROM T_KEYWORD WHERE description = 'Merged ID'). These attributes then naturally get transferred in any subsequent merges. You can use this on a stale dataset containing old IDs to easily update them to the current active account. For example:
WITH new AS ( SELECT customer_no, old_customer_no = CAST(key_value AS int) FROM dbo.TX_CUST_KEYWORD WHERE keyword_no = (SELECT keyword_no FROM dbo.T_KEYWORD WHERE description = 'Merged ID') ) SELECT old.customer_no, current_customer_no = COALESCE(new.customer_no, old.customer_no) FROM #old old LEFT JOIN new ON old.customer_no = new.old_customer_no;
Apologies for the bad initial post and subsequent edits. If I remove the spaces from "SELECT" in the first paragraph, the post fails with an error. What even!?
Another place, I guess. Haven't tried, but i guess even though it's still a text search, at least it's a smaller table overall than TA_AUDIT_TRAIL, and you can limit the search space by numeric id instead of another text label.
Being able to limit the search space by numeric ID instead of text isn't the determining factor here — it's that the TX_CUST_KEYWORD table has an index on keyword_no. TA_AUDIT_TRAIL only has indexes on customer_no and date, so you have to do a full table scan every time you want to filter to [action] = 'merged'. If TA_AUDIT_TRAIL had an index on action, the performance would be (more or less) the same.
The performance of the join between old and new could be further improved if the "new" table had an index on "old_customer_no". That could be accomplished by doing the select/cast from TX_CUST_KEYWORD into a temp table with that index instead of in a CTE.
Ugh, I was afraid of this: my "Merged ID" keywords match T_MERGED in count, but don't seem to match up to the current customer -> all past merged ids table I built. Haven't figured out what the discrepancy means at the moment.