AZZA-KHAMEES
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
Bye, Olaf.