mywebadmin
asked on
delete record duplicate finger-print from sql server 2012
we have deivce actatek finger-print
i need delete duplicate in =0 also delete duplicate out=1
i think we need
Select min(AccessLog.LOGTIME) when press in
Select max(AccessLog.LOGTIME) when press out
TABLE : AccessLog
RCID EMPLOYEEID LOGDATE LOGTIME TERMINALID INOUT EXCUSED
====== === =========== ======== ============ == =======
669849 999 17/02/2014 02:36:10 00111DA047EE 1 False
669848 999 17/02/2014 02:33:40 00111DA047EE 1 False
669847 999 17/02/2014 11:32:34 00111DA047EE 0 False
669844 999 17/02/2014 11:05:29 00111DB001A5 0 False
669843 999 17/02/2014 11:06:23 00111DB001A5 0 False
669823 999 17/02/2014 10:04:56 00111DB001A5 0 False
duplicate.JPG
i need delete duplicate in =0 also delete duplicate out=1
i think we need
Select min(AccessLog.LOGTIME) when press in
Select max(AccessLog.LOGTIME) when press out
TABLE : AccessLog
RCID EMPLOYEEID LOGDATE LOGTIME TERMINALID INOUT EXCUSED
====== === =========== ======== ============ == =======
669849 999 17/02/2014 02:36:10 00111DA047EE 1 False
669848 999 17/02/2014 02:33:40 00111DA047EE 1 False
669847 999 17/02/2014 11:32:34 00111DA047EE 0 False
669844 999 17/02/2014 11:05:29 00111DB001A5 0 False
669843 999 17/02/2014 11:06:23 00111DB001A5 0 False
669823 999 17/02/2014 10:04:56 00111DB001A5 0 False
duplicate.JPG
ASKER
it's good but i need
min time when press in
and
max time when press out
min time when press in
and
max time when press out
again: do you just want to QUERY, or also DELETE data?
for DELETE, see above, eventually we will need to adjust the query
for SELECT, maybe this?
for DELETE, see above, eventually we will need to adjust the query
for SELECT, maybe this?
select employeeid
, logdate
, min(case when inout = 1 then logtime end) min_in_1
, max(case when inout = 0 then logtime end) max_in_0
from yourtable t
group by EMPLOYEEID
, LOGDATE
ASKER
thank you Guy Hengel for help
i need delete repeat record and display output max logtime and min logtime
RCID EMPLOYEEID LOGDATE LOGTIME TERMINALID INOUT EXCUSED
====== === =========== ======== ============ == =======
669849 999 17/02/2014 02:46:10 00111DA047EE 1 False
669848 999 17/02/2014 02:23:40 00111DA047EE 1 False
669847 999 17/02/2014 08:32:34 00111DA047EE 0 False
669844 999 17/02/2014 08:05:29 00111DB001A5 0 False
669843 999 17/02/2014 08:06:23 00111DB001A5 0 False
669823 999 17/02/2014 08:04:56 00111DB001A5 0 False
when you OUT display like this max logtime
RCID EMPLOYEEID LOGDATE LOGTIME TERMINALID INOUT EXCUSED
====== === =========== ======== ============ == =======
669849 999 17/02/2014 02:46:10 00111DA047EE 1 False
at morning we need min logtime
RCID EMPLOYEEID LOGDATE LOGTIME TERMINALID INOUT EXCUSED ====== === =========== ======== ============ == =======
669823 999 17/02/2014 08:04:56 00111DB001A5 0 False
i need delete repeat record and display output max logtime and min logtime
RCID EMPLOYEEID LOGDATE LOGTIME TERMINALID INOUT EXCUSED
====== === =========== ======== ============ == =======
669849 999 17/02/2014 02:46:10 00111DA047EE 1 False
669848 999 17/02/2014 02:23:40 00111DA047EE 1 False
669847 999 17/02/2014 08:32:34 00111DA047EE 0 False
669844 999 17/02/2014 08:05:29 00111DB001A5 0 False
669843 999 17/02/2014 08:06:23 00111DB001A5 0 False
669823 999 17/02/2014 08:04:56 00111DB001A5 0 False
when you OUT display like this max logtime
RCID EMPLOYEEID LOGDATE LOGTIME TERMINALID INOUT EXCUSED
====== === =========== ======== ============ == =======
669849 999 17/02/2014 02:46:10 00111DA047EE 1 False
at morning we need min logtime
RCID EMPLOYEEID LOGDATE LOGTIME TERMINALID INOUT EXCUSED ====== === =========== ======== ============ == =======
669823 999 17/02/2014 08:04:56 00111DB001A5 0 False
again, this looks like you only want to query, so my article above should help you to solve that yourself ...
do you want to query for a single employee/date, or for the full range?
do you want to query for IN / OUT separately, or for both at the same time?
did you try my query above? what is wrong about that?
do you want to query for a single employee/date, or for the full range?
do you want to query for IN / OUT separately, or for both at the same time?
did you try my query above? what is wrong about that?
ASKER
i try your code it's work very well
delete t
from yourtable t
where exists(select null from yourtable p
where p.EMPLOYEEID = t.EMPLOYEEID
and p.INOUT = t.INOUT
and p.LOGDATE = t.LOGDATE
and p.LOGTIME < t.LOGTIME
)
but you code
delete firts punch fingerprint
in and fist punch fingerprint when
out
i need delete fist punch and last punch out for logtime
delete t
from yourtable t
where exists(select null from yourtable p
where p.EMPLOYEEID = t.EMPLOYEEID
and p.INOUT = t.INOUT
and p.LOGDATE = t.LOGDATE
and p.LOGTIME < t.LOGTIME
)
but you code
delete firts punch fingerprint
in and fist punch fingerprint when
out
i need delete fist punch and last punch out for logtime
ASKER
look at this table
id employeeid logdate logtime terminalid inout excuted
670150 999 19/02/2014 14:16:53 00111DB001A5 1 False
670152 999 19/02/2014 09:21:17 00111DB001A5 0 False
670992 999 19/02/2014 07:34:22 00111DB001D5 0 False
670392 999 19/02/2014 08:39:58 00111DB001A5 0 False
670436 999 19/02/2014 12:25:48 00111DB001D4 1 False
671021 999 19/02/2014 14:30:47 00111DB001A5 1 False
when i use you code
select employeeid, logdate
, min(case when inout = 0 then logtime end) min_in_0
, max(case when inout = 1 then logtime end) max_out_1
from AccessLog t
where EMPLOYEEID='999'
group by EMPLOYEEID , LOGDATE
output
employeeid logdate min_in_0 max_out_1
999 19/02/2014 07:34:22 14:30:47
very good
but i need delete everything except min_in_0 and max_out_1 each employeeid
do you understand me Guy Henge
id employeeid logdate logtime terminalid inout excuted
670150 999 19/02/2014 14:16:53 00111DB001A5 1 False
670152 999 19/02/2014 09:21:17 00111DB001A5 0 False
670992 999 19/02/2014 07:34:22 00111DB001D5 0 False
670392 999 19/02/2014 08:39:58 00111DB001A5 0 False
670436 999 19/02/2014 12:25:48 00111DB001D4 1 False
671021 999 19/02/2014 14:30:47 00111DB001A5 1 False
when i use you code
select employeeid, logdate
, min(case when inout = 0 then logtime end) min_in_0
, max(case when inout = 1 then logtime end) max_out_1
from AccessLog t
where EMPLOYEEID='999'
group by EMPLOYEEID , LOGDATE
output
employeeid logdate min_in_0 max_out_1
999 19/02/2014 07:34:22 14:30:47
very good
but i need delete everything except min_in_0 and max_out_1 each employeeid
do you understand me Guy Henge
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you mr Guy Hengel it's work very well
ASKER
very good
https://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
just to make sure the concept of "duplicate" is well understood
given the data, I presume you want to remove all but the "first RCID" value..
Open in new window
if that is not what you want, please clarify