We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
3 days, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
getting duplicate records from tables
Posted on 2016-07-27
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
5 Comments
Active 1 day ago
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
Active today
I would try
Bye, Olaf.
HAVING
Count(NULLIF(charindex('ENTRY',eventplace,0),1))
<>Count(NULLIF(charindex('EXIT' ,eventplace,0),1))
Bye, Olaf.
Active today
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
Active today
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month3 days, 13 hours left to enroll
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.