RadhaKrishnaKiJaya
asked on
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
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
Where is the data coming from a file, EF query, DataTable or other??
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)
ASKER
They are in a table in SQL server.
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>..
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Thank you very much. It worked!!
for info only:
an alternative that will list all rows that share the the same date/time measured to seconds.
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