Solved

need help with duplicate dates in query

Posted on 2014-12-10
3
95 Views
Last Modified: 2014-12-10
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?

MyTable

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
0
Comment
Question by:brgdotnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 240 total points
ID: 40491245
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

0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 240 total points
ID: 40491262
Try this solution:
WITH SameDate_CTE(Letter, DateEntered, Repeats)
AS
(SELECT LEFT(LastName,1), DateEntered, COUNT(1)
FROM mytable m
WHERE m.LastName LIKE 'S%' 
GROUP BY LEFT(LastName,1), DateEntered
HAVING COUNT(1)>1)

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

Open in new window

0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 20 total points
ID: 40491284
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.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question