T-sql find duplicate records when dates are in the same month and year

Question

I want to write a T-SQL statement to find duplicate record when dates are matched to the same month and year.

I managed to find duplicates if dates are exactly the same in Ms Access using this:

SELECT
    First(dbo_T_TrainingMandatory.StaffID) AS [StaffID Field],
    First(dbo_T_TrainingMandatory.TrainingCourseID) AS [TrainingCourseID Field],
    First(dbo_T_TrainingMandatory.DateTrained) AS [DateTrained Field],
    COUNT(dbo_T_TrainingMandatory.StaffID) AS NumberOfDups
FROM dbo_T_TrainingMandatory
GROUP BY dbo_T_TrainingMandatory.StaffID,
         dbo_T_TrainingMandatory.TrainingCourseID,
         dbo_T_TrainingMandatory.DateTrained

HAVING (((COUNT(dbo_T_TrainingMandatory.StaffID)) > 1)
AND ((COUNT(dbo_T_TrainingMandatory.DateTrained)) > 1));

But haven't found a solution when the day of the month are different.

Possible duplicate of [SQL grouping by month and year](http://stackoverflow.com/questions/5406170/sql-grouping-by-month-and-year) — Tab Alleman, Aug 30 '16 at 18:20
duplicate, can probably be solved by applying [this](http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype?rq=1) and [this](http://stackoverflow.com/questions/4434118/select-statement-to-find-duplicates-on-certain-fields?rq=1). OP seems to be in need of translation from msaccess/jet dialect to tsql too. — Cee McSharpface, Aug 30 '16 at 18:20

score 2 · Accepted Answer · answered Aug 30 '16 at 18:35

While I agree this is technically a duplicate, the posts listed provide older methods of achieving this outcome.

This should do it:

;WITH myCTE ([StaffID Field],[TrainingCourseID Field],[DateTrained Field],[NumberOfDups]) AS
(
    SELECT StaffID,
           TrainingCourseID,
           DateTrained,
           COUNT(2) OVER(PARTITION BY StaffID, TrainingCourseID, DATEPART(YY,DateTrained),DATEPART(MM,DateTrained))
    FROM dbo_T_TrainingMandatory
)

SELECT * 
FROM myCTE
WHERE NumberOfDups>1

If you are seeing 1900-01-01 in your data, that means the date is blank. If blank dates are acceptable, but you want to omit them here, add WHERE DateTrained <> '' AND DateTrained IS NOT NULL inside the CTE.

Thanks you nailed the problem on the head. – SoupOfStars Aug 30 '16 at 18:47 — SoupOfStars, Aug 30 '16 at 18:47

score 0 · Answer 2 · answered Aug 30 '16 at 18:24

0

You can use this query to get duplicates

; with cte as (
SELECT StaffId,DateTrained, row_number() over (partition by month(DateTrained), year(DateTrained) order by DateTrained) as rn from tablename
) select staffId, DAteTrained from cte where rn > 1

answered Aug 30 '16 at 18:24

Kannan Kandasamy

13,405
3
25
38

@ Kannan Kandasamy that gives me 8000 rows of the duplicate date 1900-01-01 00:00:00 – SoupOfStars Aug 30 '16 at 18:32

T-sql find duplicate records when dates are in the same month and year

2 Answers2