Solved

need help with duplicate dates in query

Posted on 2014-12-10
3
90 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
3 Comments
 
LVL 18

Accepted Solution

by:
SimonAdept 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 45

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 65

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now