Select Records With Same Date

[el zorro]

I have an adp front end connected to SQL Server database. I want to create a
list of records where more than one record has the same date, from a list of
about 4000 records. Right now, I can sort by date and just scroll down and
look, but it would really help if I could narrow the list to just those where
ther are multuiple instances of records with the same date.

I get the feeling that some sort of loop is required here, but I'm not sure
how to set that up. Can you help? Thanks!

 

[Sylvain Lafontaine]

On which version of SQL-Server?

To extract only those records with a duplicate, you can make a Group By and
either add a Count(*) column to display the count number for each date or
use an Having instruction to extract the duplicate. For example (untested):

Select *, (Select Count (*) From MyTable T2 on T2.MyDate = T.MyDate Group By
T2.MyDate) as CountOfMyDate
From MyTable as T

or:

Select * from MyTable as T
Where T.IdTable in (Select T2.IdTable From MyTable as T2 Group By T2.MyDate
Having Count(*) >= 2)

SQL-Server 2005 and 2008 have other special functions for doing this.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[el zorro]

Good question-- you'd think I'd know what SQL-Server version I'm using. I
think it's 2000, but I'm not sure and don't know how to find out. I use
Microsoft SQL Enterpriose Manager (v 8.0) to access the server files.

But as for my question, I don't want to collapse the records into a group
with a count; I want to continue to view other data fields for these records.

To be more specific, these are appointment records. I am looking for
instances where the same person had more than one appointment on the same
day. So, manually I can sort my list by last name then by date and look for
the same name occurring more than once on the same date. But I was wondering
if I could use some kind of filter or routine to show me only those records
where the same person shows up more than once on the same date.

Althougth I can create SELECT statements that key on fields within a single
record, I don't exactly know how to set them up to look at, say, a Name field
in Record A and compare it to the Name field in Record B, and them only
include both recoprds if there is a match.

THanks.

 

[Sylvain Lafontaine]

Did you take a look at my examples?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Sylvain Lafontaine]

However, there is an error in my second example: the IN operator must be
applied on the MyDate field and not on the ID field.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[el zorro]

Thanks, Sylvain. I didn't look closely enough at your examples-- I thought
you were just saying to do a group and count. Yes, you approach works fine.

The next step for me would be to look for consecutive dates. All
appointments where one date is followed by another record for the next day.
Any ideas there?