[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

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
0
mywebadmin
Asked:
mywebadmin
  • 6
  • 4
1 Solution
 
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
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now