Solved

need help with duplicate dates in query

Posted on 2014-12-10
3
91 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:
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 46

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

896 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

13 Experts available now in Live!

Get 1:1 Help Now