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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
please check this article, use the row number technique
https://www.experts-exchange.com/articles/3203/DISTINCT-and-GROUP-BY-and-why-does-it-not-work-for-my-query.html
https://www.experts-exchange.com/articles/3203/DISTINCT-and-GROUP-BY-and-why-does-it-not-work-for-my-query.html
I would try
Bye, Olaf.
HAVING
Count(NULLIF(charindex('ENTRY',eventplace,0),1))
<>Count(NULLIF(charindex('EXIT' ,eventplace,0),1))
Bye, Olaf.
I am not sure what you are asking for to be honest as you don't specifiy the expected result.
Here is an approach, I have added an unmatched entry to the sample data:
Here is an approach, I have added an unmatched entry to the sample data:
DECLARE @Table1 TABLE (
RecId int
, ID int
, EventDate datetime
, EventTime datetime
, EventPlace varchar(9)
, EventName varchar(7)
, EventUserID int
, EventUserName varchar(7)
)
;
INSERT INTO @Table1 (RecId, ID, EventDate, EventTime, EventPlace, EventName, EventUserID, EventUserName)
VALUES
(42696651, 4437146, '2016-07-26 00:00:00', '2016-07-27 08:39:44', 'ENTRY GF1', 'Granted', 00004449, 'A.Ameer'),
(42697053, 4438589, '2016-07-26 00:00:00', '2016-07-27 14:25:04', 'EXIT GF1', 'Granted', 00004449, 'A.Ameer'),
(42696253, 4436414, '2016-07-26 00:00:00', '2016-07-27 06:49:33', 'ENTRY GF1', 'Granted', 00009115, 'Jabber'),
(42696692, 4437291, '2016-07-26 00:00:00', '2016-07-27 09:14:20', 'EXIT GF1', 'Granted', 00009115, 'Jabber'),
(42696867, 4438070, '2016-07-26 00:00:00', '2016-07-27 12:27:48', 'ENTRY GF3', 'Granted', 00009115, 'Jabber'),
(42696868, 4438070, '2016-07-26 00:00:00', '2016-07-27 12:27:48', 'EXIT GF3', 'Granted', 00009115, 'Jabber'),
(72696868, 7438070, '2016-07-27 00:00:00', '2016-07-27 12:27:48', 'ENTRY GF3', 'Granted', 7000, 'Paul')
;
SELECT
ins.RecId
, o.RecId o_RecId
, ins.ID
, ins.EventDate
, ins.EventTime EntryTime
, o.ExitTime
, ins.EventPlace
, ins.EventName
, ins.EventUserID
, ins.EventUserName
FROM @table1 AS ins
OUTER APPLY (
SELECT TOP(1)
RecId, EventTime as ExitTime
FROM @table1 AS outs
WHERE EventPlace LIKE 'EXIT%'
AND outs.EventUserID = ins.EventUserID
AND outs.EventTime >= ins.EventTime
ORDER BY outs.EventTime ASC
) AS o
WHERE ins.EventPlace LIKE 'ENTRY%'
;
RecId o_RecId ID EventDate EntryTime ExitTime EventPlace EventName EventUserID EventUserName
-------- -------- ------- ------------- ------------- ------------- ---------- --------- ----------- -------------
42696651 42697053 4437146 2016-07-26 00:00:00 2016-07-27 08:39:44 2016-07-27 14:25:04 ENTRY GF1 Granted 4449 A.Ameer
42696253 42696692 4436414 2016-07-26 00:00:00 2016-07-27 06:49:33 2016-07-27 09:14:20 ENTRY GF1 Granted 9115 Jabber
42696867 42696868 4438070 2016-07-26 00:00:00 2016-07-27 12:27:48 2016-07-27 12:27:48 ENTRY GF3 Granted 9115 Jabber
72696868 null 7438070 2016-07-27 00:00:00 2016-07-27 12:27:48 null ENTRY GF3 Granted 7000 Paul
I assume the GF number also plays a role, so also a user having records ('ENTRY GF1'),('ENTRY GF1'),('ENTRY GF2'),('EXIT GF1'),('EXIT GF2'),('EXIT GF3') should be part of the result, even though number of ENTRY and EXIT match.
You have a classical case of mixing two atomic informations into one column. This should be Event and Place instead of EventPlace, then you can GROUP BY Place too, and compare the counts of entries and exits.
And if you'd store an ENTRY as +1 and an EXIT as -1 as a Change integer column, then this would work:
Bye, Olaf.
You have a classical case of mixing two atomic informations into one column. This should be Event and Place instead of EventPlace, then you can GROUP BY Place too, and compare the counts of entries and exits.
Declare @sampledata as table (Event Varchar(10), Place Char(3));
insert into @sampledata values
('ENTRY','GF1'),('ENTRY','GF1'),('ENTRY','GF2'),
('EXIT', 'GF1'), ('EXIT', 'GF2'),('EXIT', 'GF3');
Select Place
,Count(NULLIF(charindex('ENTRY',Event,0),0)) as CountENTRIES
,Count(NULLIF(charindex('EXIT' ,Event,0),0)) as CountEXITS
FROM @sampledata group by Place
Having Count(NULLIF(charindex('ENTRY',Event,0),0))
<> Count(NULLIF(charindex('EXIT' ,Event,0),0))
And if you'd store an ENTRY as +1 and an EXIT as -1 as a Change integer column, then this would work:
Declare @sampledata as table (Change int, Place Char(3));
insert into @sampledata values (1,'GF1'),(1,'GF1'),(1,'GF2'),(-1,'GF1'),(-1,'GF2'),(-1,'GF3');
Select Place
,Sum(Change) as NetChange
FROM @sampledata group by Place
Having Sum(Change)<>0
Bye, Olaf.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial