Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

delete record duplicate finger-print from sql server 2012

Posted on 2014-02-17
10
342 Views
Last Modified: 2014-02-20
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
0
Comment
Question by:mywebadmin
  • 6
  • 4
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39864225
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
 

Author Comment

by:mywebadmin
ID: 39864413
it's good but i need
min time  when press in
and
max time  when press out
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39864435
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:mywebadmin
ID: 39864981
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39867865
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
 

Author Comment

by:mywebadmin
ID: 39868701
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
 

Author Comment

by:mywebadmin
ID: 39870069
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39870181
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
 

Author Comment

by:mywebadmin
ID: 39873069
thank you mr  Guy Hengel it's work very well
0
 

Author Closing Comment

by:mywebadmin
ID: 39873078
very good
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question