Summary: I need to identify which records are violating a unique index to get the source file fixed.
Details: Apologies, I know this should be simple, and I've Googled before posting. I'm used to a different version of SQL where this approach would work:
select t2.cnt, t1.*
from #t1 t1
select caseid, claimnum, linenum, invoicenum, batchnum, count(*) as cnt
group by caseid, claimnum, linenum, invoicenum, batchnum
having t2.cnt > 1
I need a count by the grouping (index) variables, then re-join that number into the source data, then filter for count > 1. If my source file is 10K records, the output should be 10K records. I need all source columns in the output to analyze if it really is a duplicate record, or if another column needs to be added to the index.
The above query returns NULL for cnt for all records.
If you want a post with sample data to work with just let me know; I thought the above sufficient to explain the issue.