CODEWITHSUNDEEP
sqlsql-serverduplicates
TheWookieMan
TheWookieMan

Reputation: 1

Query to find duplicate data in multiple columns

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

Upvotes: 0

Views: 316

Answers (3)

Divyesh Gohil
Divyesh Gohil

Reputation: 11

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

Upvotes: 0

ABHAY JOHRI
ABHAY JOHRI

Reputation: 2146

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

Upvotes: 0

Maverick Sachin
Maverick Sachin

Reputation: 883

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]

Upvotes: 1

Related Questions