Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

getting duplicate records from tables

Posted on 2016-07-27
5
Medium Priority
?
67 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
4 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

824 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