Finding Duplicate Records 1

[Terpsfan]

I am trying to run a stored procedure that will display invoices that have the same invoice amount as well as invoice date. This would have to be grouped by the duplicate invoices.

Some of the fields are InvoiceID, InvoiceDate, InvoiceAmount

 

[nigelrivett]

select InvoiceDate, InvoiceAmount
from invoices
group by InvoiceDate, InvoiceAmount
having count(*) > 1

if you want the whole invoice
select *
from invoice i1
join
(select InvoiceDate, InvoiceAmount
from invoices
group by InvoiceDate, InvoiceAmount
having count(*) > 1
) i2
on i1.InvoiceDate = i2.InvoiceDate
and i1.InvoiceAmount = i2.InvoiceAmount
order by InvoiceDate, InvoiceAmount





======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Terpsfan]

Thanks a million, Nigel. I knew I had to do some sort of self join but couldn't get it to work right. I will use this example as a template for the future.

 

[Terpsfan]

If you are so kind, I have another question to ask. I am running stored procedures against some very large tables, some having over 50 fields and 5 million records.

I need to run reports off of these stored procedures in order to build a record source. I'm finding it is taking an agonizingly long time to run these procs even with where clauses.

I was thinking of creating a temp table that I could prefilter the data and run the stored procedure against the temp table.

Do you think this is a good idea? What's the best way of approaching this? Thanks