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
Bye, Olaf.