Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

getting duplicate records from tables

Posted on 2016-07-27
5
Medium Priority
?
65 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
[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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 672 total points (awarded by participants)
ID: 41730710
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41730733
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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 664 total points (awarded by participants)
ID: 41731020
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 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 664 total points (awarded by participants)
ID: 41731037
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

704 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