Link to home
Start Free TrialLog in
Avatar of AZZA-KHAMEES
AZZA-KHAMEESFlag for Bahrain

asked on

getting duplicate records from tables

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

Open in new window


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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I would try

HAVING
  Count(NULLIF(charindex('ENTRY',eventplace,0),1))
<>Count(NULLIF(charindex('EXIT' ,eventplace,0),1))

Open in new window


Bye, Olaf.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial