If we define duplicate rows as rows where the following attributes are equal: attendace_date, staff_id, working_year, [hours] and that the original row within them is the row with the smallest attendance_id, the following statement identifies duplicate rows (you see the attendance_id, so you can easily remove them if needed):

;WITH cte AS

(SELECT attendance_id, attendace_date, staff_id, working_year, [hours],

ROW_NUMBER() OVER(PARTITION BY attendace_date, staff_id, working_year, [hours]

ORDER BY attendance_id ASC) rn

FROM dbo.staff_attendance

)

SELECT * FROM cte WHERE rn > 1;

Note: the statement uses the table defined by Eirikur Eiriksson. Thanks to Erikur and next time please use the same apporach to define and populate tables by yourself.

Another note: When you want to remove duplicates you need to define them first. This sounds maybe trivial, but sometimes your perception of duplicates does not correspond to the one of developers or business guys. Simple define when two rows are duplicates (i.e. which columns must be equal) and also what is the original row within them (the smallest Id, or highest Id, timestamp whatever). This will take not more than a minute but can avoid potential misunderstandings and misinterpretations.