SQL Select Duplicate records by date

Question

I have this:

SELECT        ActionDateTime, [Action Location], FirstName, MiddleName, LastName, 
              IDNumber, IDNumber2, IDNumber3, Department, CardData_BadgePass, Location, Birthdate
FROM          dbo.EmployeeEntityTracking AS t
WHERE EXISTS    (SELECT  CAST(ActionDateTime AS DATE) AS Expr1, [Action Location], FirstName, 
                         MiddleName, LastName, IDNumber, IDNumber2, IDNumber3, Department, CardData_BadgePass, Location, Birthdate
                 FROM            dbo.EmployeeEntityTracking AS x
                 WHERE        (IDNumber = t.IDNumber) AND (ActionDateTime <> t.ActionDateTime))

Which returns records with duplicates, but I need to filter by date. (Query returns duplicates but all dates. I need duplicates on each date.

Answer 1

First it is not necessary to list all columns in the exists clause, just replace them with any fixed value, because you are looking for the existence of rows not looking to return columns values

Because the question is not so clear, i provided many suggestions based on the cases that you maybe asking for:

1) If you are looking to get duplicates based on IDNUMBER and ActionDateTime

SELECT * FROM (

SELECT      DISTINCT  ActionDateTime, [Action Location], FirstName, MiddleName, LastName, 
              IDNumber, IDNumber2, IDNumber3, Department, CardData_BadgePass, Location, Birthdate
              ,COUNT(ActionDateTime) OVER(PARTITION BY ActionDateTime,IDNUMBER) AS [ItemCount]
FROM          dbo.EmployeeEntityTracking AS t
WHERE EXISTS    (SELECT         1
                 FROM            dbo.EmployeeEntityTracking AS x
                 WHERE        (x.IDNumber = t.IDNumber) AND (x.ActionDateTime <> t.ActionDateTime))) AS T1
WHERE T1.ItemCount > 1
ORDER BY T1.ActionDateTime

2) If i understand what you are looking for, you may use DISTINCT function to eliminate duplicates based on all columns

SELECT      DISTINCT  ActionDateTime, [Action Location], FirstName, MiddleName, LastName, 
              IDNumber, IDNumber2, IDNumber3, Department, CardData_BadgePass, Location, Birthdate
FROM          dbo.EmployeeEntityTracking AS t
WHERE EXISTS    (SELECT         1
                 FROM            dbo.EmployeeEntityTracking AS x
                 WHERE        (x.IDNumber = t.IDNumber) AND (x.ActionDateTime <> t.ActionDateTime))

3) If you are looking for rows that have the same duplicated dates

SELECT * FROM (

    SELECT      DISTINCT  ActionDateTime, [Action Location], FirstName, MiddleName, LastName, 
                  IDNumber, IDNumber2, IDNumber3, Department, CardData_BadgePass, Location, Birthdate
                  ,COUNT(ActionDateTime) OVER(PARTITION BY ActionDateTime) AS [DateCount]
    FROM          dbo.EmployeeEntityTracking AS t
    WHERE EXISTS    (SELECT         1
                     FROM            dbo.EmployeeEntityTracking AS x
                     WHERE        (x.IDNumber = t.IDNumber) AND (x.ActionDateTime <> t.ActionDateTime))) AS T1
WHERE T1.DateCount > 1
ORDER BY T1.ActionDateTime

4) If you are looking to eliminate all rows that contains the same date:

SELECT * FROM (

    SELECT      DISTINCT  ActionDateTime, [Action Location], FirstName, MiddleName, LastName, 
                  IDNumber, IDNumber2, IDNumber3, Department, CardData_BadgePass, Location, Birthdate
                  ,COUNT(ActionDateTime) OVER(PARTITION BY ActionDateTime) AS [DateCount]
    FROM          dbo.EmployeeEntityTracking AS t
    WHERE EXISTS    (SELECT         1
                     FROM            dbo.EmployeeEntityTracking AS x
                     WHERE        (x.IDNumber = t.IDNumber) AND (x.ActionDateTime <> t.ActionDateTime))) AS T1
WHERE T1.DateCount < 2
ORDER BY T1.ActionDateTime

Answer 2

Try using group by:

SELECT CAST(ActionDateTime AS DATE)
    ,[Action Location]
    ,FirstName
    ,MiddleName
    ,LastName
    ,IDNumber
    ,IDNumber2
    ,IDNumber3
    ,Department
    ,CardData_BadgePass
    ,Location
    ,Birthdate
FROM dbo.EmployeeEntityTracking AS t
GROUP BY
 CAST(ActionDateTime AS DATE)
,[Action Location]
,FirstName
,MiddleName
,LastName
,IDNumber
,IDNumber2
,IDNumber3
,Department
,CardData_BadgePass
,Location
,Birthdate
HAVING COUNT(IDNumber) > 1 

PS: It will show only duplicates for all columns, if you want only by the ID and date, try to exclude all of the items.