Solved

getting duplicate records from tables

Posted on 2016-07-27
5
39 Views
Last Modified: 2016-10-07
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
0
Comment
Question by:AZZA-KHAMEES
  • 2
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 168 total points (awarded by participants)
Comment Utility
0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
I would try

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

Open in new window


Bye, Olaf.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 166 total points (awarded by participants)
Comment Utility
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:
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')
;

Open in new window

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%'
;

Open in new window

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  

Open in new window

0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 166 total points (awarded by participants)
Comment Utility
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.

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))

Open in new window


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

Open in new window


Bye, Olaf.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL 2012 and SQL 2014 always on 9 23
SQL Server memory Issue 7 73
Azure SQL DB? 3 13
How to simplify my SQL statement? 9 10
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now