Mental block (duplicate query)

[Chumley40]

I am trying to write stored proc that gives me duplicates. The way it is written now is something like:
select * from table1 where entereddate between getdate()-100 and getdate()
I put that into a table variable (tmptable1), then do another query.
Select * from table2
join table2 on
table2.indexid <> table1.indexid and
name = name and client= client.

What I would like to see is all the duplicates of table one regardless of when they were entered. So, in other words: Start with the entered date to pull the potentially duplicated fields, then look at all the data and pull out those dates without regard to entered.

Does that make sense, and can anyone help?

 

[Chumley40]

Sorry, I typed too quickly. The second part should be

Select * from table2
join table1 on
table2.indexid <> table1.indexid and
table2.name = table1.name and table2.client= table1.client.


What I would like to see is all the duplicates of table one regardless of when they were entered. So, in other words: Start with the entered date to pull the potentially duplicated fields, then look at all the data from the database table and pull out that row into table 2 without regard to entered date.

 

[ca8msm]

If I understand you correctly, you just need to group on the columns that you consider make up the duplicate and add a having clause to only return those with more than one record e.g.

select name, address
from mytable
group by name, address
having count(*) > 1

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[gmmastros]

Try this...

Select Name, Client
From   TableName
Where  EnteredDate Between GetDate()-100 And GetDate
Group By Name, Client
Having Count(*) > 1

If this works for you, and you want me to explain it, just let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chumley40]

Thanks, both of you, but the problem is the duplicate data is not in the first table when the dates are applied. I get the base list of what I want. then I need to go back into that table and pull out duplicates of that original list without the date applied. So the second time I am looking at the whole table. The first time I am looking at a snapshot by date.

 

[gmmastros]

My apologies for not understanding your problem. Can you please post some sample data and expected results? This will make it easier for us to help you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chumley40]

It looks like I got it. I needed to do another join on the results of the first and second selects so that I didn't drop the fields from the first query.

Thanks.