Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Merge count by grouping variables back into source table Expand / Collapse
Author
Message
Posted Tuesday, July 15, 2014 11:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 28, 2014 3:52 AM
Points: 7, Visits: 50
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
left join
(
select caseid, claimnum, linenum, invoicenum, batchnum, count(*) as cnt
from #t1
group by caseid, claimnum, linenum, invoicenum, batchnum
) t2
on t1.caseid=t2.caseid
and t1.claimnum=t2.claimnum
and t1.linenum=t2.linenum
and t1.invoicenum=t2.invoicenum
and t1.batchnum=t2.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.

Thanks...
Post #1592901
Posted Tuesday, September 16, 2014 4:49 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:18 AM
Points: 610, Visits: 283
Scott In Sydney (7/15/2014)
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
left join
(
select caseid, claimnum, linenum, invoicenum, batchnum, count(*) as cnt
from #t1
group by caseid, claimnum, linenum, invoicenum, batchnum
) t2
on t1.caseid=t2.caseid
and t1.claimnum=t2.claimnum
and t1.linenum=t2.linenum
and t1.invoicenum=t2.invoicenum
and t1.batchnum=t2.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.

Thanks...


Hi,

your idea is correct; you have just made a "typo".
You perform the grouping in the nested query and not in the main query. Thus you can't use HAVING in the main query.

So: Just change the last row of your query to use a WHERE instead and it will work properly.

Good luck!

/M
Post #1614024
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse