need help with duplicate dates in query

I have a table below with 3 columns of data. The table has hundreds of records in it, and I only show 4 records here.
I need a query that will query MyTable to determine if any people who have a last name starting with "S" also
have "DateEntered" dates which are duplicates.

I don't know hot to do this. Can someone help me out?


AssignedId      LastName       DateEntered
----------              --------       ------------
1                        Jones         2014-12-07 08:20:58.407
2                       Smith         2014-12-07 08:27:39.053
3                       Solter        2014-12-07 08:31:06.547
4                       Sekk          2014-12-07 08:31:06.547
Who is Participating?
SimonConnect With a Mentor Commented:
This finds EXACT duplicates on DateEntered
select lastname,dateEntered from MyTable 
where lastname like 'S%'
group by lastname,dateentered
having count(*)>1

Open in new window

If you want to count all times within the same date as duplicates:
select lastname,dateadd(dd,0,dateEntered) as [EnteredDate] from MyTable 
where lastname like 'S%'
group by lastname,dateadd(dd,0,dateEntered)
having count(*)>1

Open in new window

Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Try this solution:
WITH SameDate_CTE(Letter, DateEntered, Repeats)
(SELECT LEFT(LastName,1), DateEntered, COUNT(1)
FROM mytable m
WHERE m.LastName LIKE 'S%' 
GROUP BY LEFT(LastName,1), DateEntered

FROM mytable m
INNER JOIN SameDate_CTE c ON c.DateEntered=m.DateEntered
WHERE m.LastName LIKE 'S%'

Open in new window

Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Simon's answer is correct based on your question.

If you'd like more reading material and a few laughs, I have an article on SQL Server Deleting Duplicate Rows that is a grab-bag on how most people deal with duplicate rows around here.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.