Need help with a query

Hi Experts,

I need to find the duplicate records when the date and time is exactly same for 'StdTimeIn'. Sometimes the millisecond is different. But if the date and hh:mm:ss is same then I need those records. I have attached the data.

I want the records-
473      105      2016-08-31 15:19:56.767      2016-08-31 20:00:00.110      08312016
474      105      2016-08-31 15:19:56.767      2016-08-31 20:00:00.110      08312016

476      534      2016-08-31 16:22:23.537      2016-08-31 16:23:52.320      08312016
478      534      2016-08-31 16:22:23.540      2016-08-31 16:23:51.633      08312016
477      534      2016-08-31 16:22:23.537      2016-08-31 16:23:51.357      08312016

Thanks in advance.
Duplicates.txt
RadhaKrishnaKiJayaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Randy PooleConnect With a Mentor Commented:
I created a temporary table #t, so replace that with your actual table name
select * from #t where convert(varchar,StdTimeIn,120) in (select convert(varchar,StdTimeIn,120) dy from #t group by convert(varchar,StdTimeIn,120) having count(*)>1)

Open in new window

0
 
Fernando SotoRetiredCommented:
Where is the data coming from a file, EF query, DataTable or other??
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT StdLogID, UserID, StdTimeIn, StdTimeout, TodaysDate
FROM TableName T1
WHERE EXISTS (SELECT 1
        FROM TableName T2
        WHERE T1.UserID=T2.UserID
         AND T1.StdTimeIn=T2.StdTimeIn
         AND T1.StdTimeout=T2.StdTimeOut
         AND T1.TodaysDate=T2.TodaysDate
         AND T1.StdLogID<>T2.StdLogID)

Open in new window

0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
RadhaKrishnaKiJayaAuthor Commented:
They are in a table in SQL server.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, eyeballleth thy article SQL Server Delete Duplicate Rows Solutions, which is a sample code and image-rich tutorial on how to pull that off.

Applied to your code though <air code, holy cow make a backup first>..
;with cte as (
SELECT UserID, StdTimeIn, 
   ROW_NUMBER() OVER (PARTITION BY UserId, convert(varchar, StdTimeIn ,120) ORDER BY (SELECT NULL)) as row_number
FROM your_table) 
DELETE FROM cte WHERE row_number > 1

Open in new window

0
 
RadhaKrishnaKiJayaAuthor Commented:
Hi Mr. Vitor,

I am getting the records using your query. But I do not want to match the miliseconds.  I want to check for todays date,  where StdTimeIn should same tiill yyyy-mm-dd hh:mm:ss. The millisecond might be different.

Thanks for your help.
0
 
RadhaKrishnaKiJayaAuthor Commented:
Thank you very much. It worked!!
0
 
PaulCommented:
for info only:
an alternative that will list all rows that share the the same date/time measured to seconds.

SELECT
      *
FROM (
      SELECT
            *
          , COUNT(*) OVER(PARTITION BY CONVERT(varchar, StdTimeIn, 120) ) AS count_of
      FROM your_table
      ) D
WHERE count_of > 1

Open in new window

0
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.