Solved

getting duplicate records from tables

Posted on 2016-07-27
5
61 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 168 total points (awarded by participants)
ID: 41730710
0
 
LVL 29

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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 166 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 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 166 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Percentage Formula 7 33
DMV Script to find how many times statistics are utilized 2 30
T-SQL Query 9 35
Group ordersum by orderdate 3 18
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

738 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