Solved

delete record duplicate finger-print from sql server 2012

Posted on 2014-02-17
10
329 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 142

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 142

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
 

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 142

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 142

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

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…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now