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
mywebadminAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
ok, so you want this:
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 ( ( t.INOUT = 1 AND p.LOGTIME > t.LOGTIME )
                    OR ( t.INOUT = 0 AND p.LOGTIME < t.LOGTIME )
                           )
) 

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's start by reading this article I wrote:
http://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..
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
)

Open in new window


if that is not what you want, please clarify
0
 
mywebadminAuthor Commented:
it's good but i need
min time  when press in
and
max time  when press out
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
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   
                                            

Open in new window

0
 
mywebadminAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
 
mywebadminAuthor Commented:
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
0
 
mywebadminAuthor Commented:
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
0
 
mywebadminAuthor Commented:
thank you mr  Guy Hengel it's work very well
0
 
mywebadminAuthor Commented:
very good
0
All Courses

From novice to tech pro — start learning today.