?
Solved

delete record duplicate finger-print from sql server 2012

Posted on 2014-02-17
10
Medium Priority
?
354 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
[X]
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
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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 1000 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

777 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