How can i remove duplicate rows with in my sql server database

Question

I have this table of attendance system with columns dateinout, checkin, checkout etc. user punch in when come to office and punch out when go out. system retrieve data two time from fingerprint scanner machine. I want to delete rows with more than one count in same date where punch in is between 7am to 11 am also the rows with check out with same date in between 11 am to 6 pm.

    SELECT
    a.Logid, 
    a.Userid, 
    a.CheckTime,  
    a.Name  

FROM Checkinout a 
JOIN
(SELECT
    userid,
    name,  
    dateinout,  
    Intime,
    Outtime

    FROM att
    WHERE Intime BETWEEN '07:00:00.0000000' AND '11:00:00.0000000'
    AND userid= 37
    GROUP BY userid, dateinout, Intime, Outtime, name
    HAVING COUNT(Intime)>1) b
    ON a.Userid= b.userid
    ORDER BY CheckTime ASC;

possibly duplicate of [This](https://stackoverflow.com/questions/56437256/how-to-simply-delete-duplicate-records-in-sql-server/56437329#56437329) — Raka, Jun 14 '19 at 10:06

score 0 · Answer 1 · answered Jun 14 '19 at 06:28

0

You can do step by step. Firstly, find insert count in time range:

SELECT COUNT(Logid), userid
FROM Checkinout
WHERE Intime BETWEEN '07:00:00.0000000' AND '11:00:00.0000000'
GROUP BY Logid
HAVING COUNT(Logid) > 1

after, you got list, you can distract by property how you need

answered Jun 14 '19 at 06:28

malisasmaz

99
1
7

The problem is that whenever the fingerprint is scanned it generates the new automatic Logid for everytime finger scanned so it consider each finger as unique record but we know that is not the case and thus we need some different condition such as same count( date)>1 where userid is same and also count for intime between 7 and 11 >1 but whenever I try to use two counts in my query it generates the error. – Jun 15 '19 at 07:40
for using two counts [link](https://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query) – malisasmaz Jun 17 '19 at 05:01
Thanks you for the link – Oct 09 '19 at 11:43

score 0 · Accepted Answer · answered Jun 14 '19 at 10:06

You can use cte to delete duplicates from the att table based on your grouping criteria.

;WITH CTE AS(
SELECT
row_number() over (partition by  userid,dateinout, Intime, Outtime order by date) AS ROWNUMBER,
        userid,
        dateinout,  
        Intime,
        Outtime
    FROM 
        att
    WHERE 
        Intime BETWEEN '07:00:00.0000000' AND '11:00:00.0000000'
        AND userid = 37
 )

 DELETE FROM CTE WHERE ROWNUMBER>1

How can i remove duplicate rows with in my sql server database

2 Answers2