troubleshooting Question

getting duplicate records from tables

Avatar of AZZA-KHAMEES
AZZA-KHAMEESFlag for Bahrain asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
4 Comments3 Solutions128 ViewsLast Modified:
Hi Experts
i am facing a complicated issues in a very important table, this table is big with about 1000 records

RecId              ID                EventDate      EventTime                              EventPlace      EventName      EventUserID      EventUserName
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
42696651      4437146       2016-07-26       2016-07-27 08:39:44.000      ENTRY GF1    Granted          00004449      A.Ameer                            
42697053      4438589      2016-07-26    2016-07-27 14:25:04.000      EXIT GF1        Granted          00004449      A.Ameer                            
42696253      4436414      2016-07-26       2016-07-27 06:49:33.000      ENTRY GF1    Granted          00009115      Jabber                          
42696692      4437291      2016-07-26    2016-07-27 09:14:20.000      EXIT GF1        Granted          00009115      Jabber                          
42696867      4438070      2016-07-26       2016-07-27 12:27:48.000      ENTRY GF3    Granted          00009115      Jabber
42696868      4438070      2016-07-26       2016-07-27 12:27:48.000      EXIT GF3        Granted          00009115      Jabber
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
as you can see each record have ENTRY and EXIT for the same EventUserName, and the same user can have a multiple ENTRY and multiple EXIT, but in some cases the ENTRY may not have an EXIT, for that i wrote an query to get me the odd number records
SELECT CAST(CONVERT(varchar, EventDate, 101)as datetime) EventDate,CONCAT(YEAR(EventDate),'-',
MONTH(EventDate),'-',DAY(EventDate)) as E2,EventUserId,EventUserName,count(*) as Records 
FROM [Attendance].[dbo].[HistFID] 
where eventdate='2016-07-26' 
group by EventDate,EventUserName,EventUserId 
having (count(*) %2) <> 0

but this is not enough, as i may got a positive number of records but with no exit or no entry, for example

RecId              ID                EventDate      EventTime                              EventPlace      EventName      EventUserID      EventUserName
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
42696651      4437146       2016-07-26       2016-07-27 08:39:44.000      ENTRY GF1    Granted          00004449      A.Ameer                            
42697053      4438589      2016-07-26    2016-07-27 14:25:04.000      EXIT GF1        Granted          00004449      A.Ameer                            
42696253      4436414      2016-07-26       2016-07-27 06:49:33.000      ENTRY GF1    Granted          00009115      Jabber                          
42696692      4437291      2016-07-26    2016-07-27 09:14:20.000      ENTRY GF1    Granted          00009115      Jabber                          
42696867      4438070      2016-07-26       2016-07-27 12:27:48.000      ENTRY GF3    Granted          00009115      Jabber
42696868      4438070      2016-07-26       2016-07-27 12:27:48.000      EXIT GF3        Granted          00009115      Jabber

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
as you can see in the record in bold text

any suggestion
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros