Query to find duplicate data in multiple columns

Question

I am new to SQL and have been unable to find any articles to explain the way I need to filter my data in order to pull duplicate tickets, from within a ticketing system. I need to check if a store submits multiple tickets in the same category on the same date. Below is a sample of what I'm working with. Any assistance would be greatly appreciated!

USE [HelpDesk]
declare 
@date1 date = '5-1-2017',
@date2 date = '6-2-2017' 

SELECT  [SectionName]
,[CategoryName]
,[RequesterName]
,[IssueDate]
,[StatusId]
FROM [HelpDesk].[dbo].[uvTESTMasterQueryIssues]
WHERE [IssueDate] > @date1 AND [IssueDate] < @date2;

Data and Results

MySQL and SQL Server are not the same. Please remove the RDBMS you are not using. — SS_DBA
– SS_DBA, Commented Jun 15, 2017 at 14:15
Add a Group By and Having Count(1) > 1 to your Select statement — SS_DBA
– SS_DBA, Commented Jun 15, 2017 at 14:16
Depending on the datatype for IssueDate, if it's DateTime, you will need to Cast as Date to get same dates. — SS_DBA
– SS_DBA, Commented Jun 15, 2017 at 14:23

Maverick Sachin · Accepted Answer · 2017-06-15 14:21:30Z

1

I didn't get a chance to run or try it out but it should be something like this in SQL Server:

-- Number of occurrences of Duplicate records
SELECT [SectionName]
    ,[CategoryName]
    ,[RequesterName]
    ,[IssueDate]
    ,[StatusId]
    ,COUNT(*) AS [NUMBER OF OCCURENCES]
FROM [dbo].[uvTESTMasterQueryIssues]
WHERE [IssueDate] > @date1
    AND [IssueDate] < @date2
GROUP BY [SectionName]
    ,[CategoryName]
    ,[RequesterName]
    ,[IssueDate]
    ,[StatusId]
HAVING COUNT(*) > 1
ORDER BY [SectionName]

answered Jun 15, 2017 at 14:21

Maverick Sachin

8837 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ABHAY JOHRI · Accepted Answer · 2017-06-15 14:31:40Z

0

You can find duplicate records using group by, Like if you want to find duplicate record based on Category and requesterName then execute below query will give you result :

SELECT  [CategoryName]
,[RequesterName]
,Count(*)
FROM [HelpDesk].[dbo].[uvTESTMasterQueryIssues]
WHERE [IssueDate] > @date1 AND [IssueDate] < @date2
GROUP BY
    RequesterName, CategoryName
HAVING 
    COUNT(*) > 1

edited Jun 15, 2017 at 14:31

answered Jun 15, 2017 at 14:22

ABHAY JOHRI

2,1541 gold badge18 silver badges19 bronze badges

5 Comments

SS_DBA Over a year ago

Your group by needs to contain all columns that are not being aggregated.

ABHAY JOHRI Over a year ago

But by this way you can find the duplicate records, remove the other columns not needed. Am I missing anything please let me know so that I can understand better

SS_DBA Over a year ago

Did you run this code? This will give an error. That's all I'm saying. If you suggest an answer, please make sure it runs without errors.

ABHAY JOHRI Over a year ago

Let me give a try and will update you ...Give me some time. Please share your create table query(your DML) so that I can test better. As I was not having the correct schema of table that's why didn't executed that ..Just give the rough Idea used in past.

SS_DBA Over a year ago

This will not give what OP wants. They want to know if an issue was submitted more than once on same date. If you remove the date, then you are most likely to get duplicates, but could be for different dates.

Divyesh Gohil · Accepted Answer · 2017-06-16 04:57:09Z

0

It's a simple query, you can use Group by and having to achieve what you want.

Please refer: https://www.w3schools.com/sql/sql_having.asp

create table #temp (

IssueID int,
SectionName varchar(50),
CategoryName varchar(50),
RequesterName varchar(50),
IssueDate date

) insert into #temp values(123,'Maintenance','Other Alarms','Store 0009550','2017-05-17') insert into #temp values(124,'Maintenance','Other Alarms','Store 0003561','2017-05-17') insert into #temp values(125,'Maintenance','Other Alarms','Store 0009550','2017-05-17') insert into #temp values(126,'Maintenance','Other Alarms','Store 0003561','2017-05-17') insert into #temp values(127,'Maintenance','Target Move','Store 0003561','2017-05-17') insert into #temp values(128,'Maintenance','Other Alarms','Store 0007426','2017-05-17') insert into #temp values(129,'Maintenance','Target Move','Store 0007750','2017-05-17')

select * from #temp

declare @date1 date = '5-1-2017' declare @date2 date = '6-2-2017'

;with cts as ( select [CategoryName] ,[IssueDate],RequesterName FROM #temp WHERE [IssueDate] between @date1 and @date2 Group by CategoryName, [IssueDate],RequesterName having count(*)>1 )

select t.* from #temp t inner join cts ON t.CategoryName=cts.CategoryName and t.IssueDate=cts.IssueDate and t.RequesterName=cts.RequesterName order by t.RequesterName,t.IssueID

drop table #temp

edited Jun 16, 2017 at 4:57

answered Jun 15, 2017 at 14:21

Divyesh Gohil

113 bronze badges

1 Comment

SS_DBA Over a year ago

I know you're not up to 50 yet to comment, but this is a comment, not an answer. Post a valid answer of code. Take their code and make it work for the desired result. You'll get more points doing that. :-)

Collectives™ on Stack Overflow

Query to find duplicate data in multiple columns

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related