Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Duplicate Data in 5 Minutes Puzzle

In this puzzle we have to find the duplicate data within 5 minutes of each other, the combination used for duplicate data is Individual ID and message text columns Please check the sample input and expected output.

Sample Input

SalesSiteID MessageID IndividualId MessageText DateAdded
103887 1 1 Pawan 2017-12-14 12:49:22.373
103887 2 1 Pawan 2017-12-14 12:49:24.373
103887 3 2 Sharlee 2017-12-14 12:49:34.377
103887 4 2 Sharlee 2017-12-14 12:50:36.377
103887 5 3 Ramesh 2017-12-14 12:50:39.380
103887 6 3 Ramesh 2017-12-14 12:50:39.380
103887 7 3 Ramesh 2017-12-14 12:51:19.380
103887 8 3 Ramesh 2017-12-14 13:01:21.380
103887 9 4 Avika 2017-12-14 13:03:23.383
103887 10 5 Panav 2017-12-14 13:03:23.387

Expected Output

MessageID MessageID IndividualID MessageText DateAdded DateAdded
1 2 1 Pawan 2017-12-14 12:49:22.373 2017-12-14 12:49:24.373
3 4 2 Sharlee 2017-12-14 12:49:34.377 2017-12-14 12:50:36.377
5 6 3 Ramesh 2017-12-14 12:50:39.380 2017-12-14 12:50:39.380
5 7 3 Ramesh 2017-12-14 12:50:39.380 2017-12-14 12:51:19.380
6 7 3 Ramesh 2017-12-14 12:50:39.380 2017-12-14 12:51:19.380

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE DuplicateFiveMinutes
(
	 SalesSiteID BIGINT
	,MessageID BIGINT
	,IndividualId BIGINT
	,MessageText VARCHAR(200)
	,DateAdded DATETIME
)
GO

INSERT INTO DuplicateFiveMinutes VALUES (103887, 1,1,'Pawan', GETDATE());

WAITFOR DELAY '00:00:02';

INSERT INTO DuplicateFiveMinutes VALUES (103887, 2,1,'Pawan', GETDATE());

WAITFOR DELAY '00:00:10';

INSERT INTO DuplicateFiveMinutes VALUES (103887, 3,2,'Sharlee', GETDATE());

WAITFOR DELAY '00:01:02';

INSERT INTO DuplicateFiveMinutes VALUES (103887, 4,2,'Sharlee', GETDATE());

WAITFOR DELAY '00:00:03';

INSERT INTO DuplicateFiveMinutes VALUES (103887, 5,3,'Ramesh', GETDATE());

INSERT INTO DuplicateFiveMinutes VALUES (103887, 6,3,'Ramesh', GETDATE());

WAITFOR DELAY '00:00:40';

INSERT INTO DuplicateFiveMinutes VALUES (103887, 7,3,'Ramesh', GETDATE());

WAITFOR DELAY '00:10:02';

INSERT INTO DuplicateFiveMinutes VALUES (103887, 8,3,'Ramesh', GETDATE());

WAITFOR DELAY '00:02:02';

INSERT INTO DuplicateFiveMinutes VALUES (103887, 9,4,'Avika', GETDATE());

INSERT INTO DuplicateFiveMinutes VALUES (103887, 10,5,'Panav', GETDATE());

WAITFOR DELAY '00:02:02';

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1 | Using JOINS

--

SELECT m1.MessageID,
       m2.MessageID,
       m2.IndividualID ,
       m2.MessageText ,
       m1.DateAdded ,
       m2.DateAdded
FROM   DuplicateFiveMinutes m1
       INNER JOIN DuplicateFiveMinutes m2 ON m2.IndividualID = m1.IndividualID AND m2.MessageText = m1.MessageText
WHERE  m1.SalesSiteID = 103887
       AND m2.MessageID > m1.MessageID
       AND DATEDIFF(mi,m1.DateAdded,m2.DateAdded) <= 5  /* The time difference should be 5 mins*/

--

Output

--

/*------------------------
SELECT m1.MessageID,
       m2.MessageID,
       m2.IndividualID ,
       m2.MessageText ,
       m1.DateAdded ,
       m2.DateAdded
FROM   DuplicateFiveMinutes m1
       INNER JOIN DuplicateFiveMinutes m2 ON m2.IndividualID = m1.IndividualID AND m2.MessageText = m1.MessageText
WHERE  m1.SalesSiteID = 103887
       AND m2.MessageID > m1.MessageID
       AND DATEDIFF(mi,m1.DateAdded,m2.DateAdded) <= 5  /* The time difference should be 5 mins*/
------------------------*/
MessageID            MessageID            IndividualID         MessageText           DateAdded               DateAdded
-------------------- -------------------- -------------------- --------------------- ----------------------- -----------------------
1                    2                    1                    Pawan                 2017-12-14 12:49:22.373 2017-12-14 12:49:24.373
3                    4                    2                    Sharlee               2017-12-14 12:49:34.377 2017-12-14 12:50:36.377
5                    6                    3                    Ramesh                2017-12-14 12:50:39.380 2017-12-14 12:50:39.380
5                    7                    3                    Ramesh                2017-12-14 12:50:39.380 2017-12-14 12:51:19.380
6                    7                    3                    Ramesh                2017-12-14 12:50:39.380 2017-12-14 12:51:19.380

(5 row(s) affected)


                                            
--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com