Tags

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


T-SQL Query | [SQL | Find Duplicate Puzzle ] – In the puzzle we have to find the duplicate values from a duplicate table where duplicate values are more than 1. Please check the sample input and expected output for details.

Sample Input

ID EmpName EmpDate
1 Pawan 05-01-2014
1 Pawan 05-03-2014
1 Pawan 05-02-2014
4 Manisha 05-07-2014
5 Sharlee 05-09-2014
6 Barry 05-02-2014
7 Jyoti 05-04-2014
7 Jyoti 05-05-2014

Expected Output

EmpName DuplicateCount
Pawan 3
Jyoti 2

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script Use the below script to generate the source table and fill them up with the sample data.


--Create table 
CREATE TABLE TESTDuplicateCount ( ID INT ,EmpName VARCHAR(100) 
,EmpDate DATETIME ) 
GO 

--Insert Data INSERT INTO TESTDuplicateCount(ID,EmpName,EmpDate) 
VALUES 
(1,'Pawan','2014-01-05'),
(2,'Pawan','2014-03-05'), 
(3,'Pawan','2014-02-05'), 
(4,'Manisha','2014-07-05'), 
(5,'Sharlee','2014-09-05'), 
(6,'Barry','2014-02-05'), 
(7,'Jyoti','2014-04-05'), 
(8,'Jyoti','2014-05-05') 

--Check data 
SELECT ID,EmpName,EmpDate FROM TESTDuplicateCount

UPDATE – 24-Apr-2015 – Solution 1 


--

SELECT EmpName , COUNT(*) DuplicateCount FROM TESTDuplicateCount
GROUP BY EmpName 
HAVING COUNT(*) > 1
ORDER BY EmpName DESC

--

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

Keep Learning

http://MSBISkills.com