Remove duplicate data
-
Hi
Below is the create table and insert script.
I am attaching current output and expected output.
USE master
GO
CREATE TABLE [dbo].[test_07052023](
[EMAILS SENT] [float] NULL,
[mmClientID] [float] NULL,
[Report Run Date] [nvarchar](4000) NULL,
[Issue Name] [varchar](4) NOT NULL,
[Issue Year] [int] NOT NULL,
[Product] [varchar](17) NOT NULL,
[Rate Card Name] [varchar](9) NOT NULL,
[Customer ID] [int] NOT NULL,
[Company/Database Name] [varchar](90) NULL,
[Ad Color] [varchar](50) NULL,
[Ad Size] [varchar](100) NULL,
[Sales Rep] [varchar](101) NOT NULL,
[Quantity] [varchar](1) NOT NULL,
[Net] [money] NULL,
[Gross] [money] NULL,
[Rate Card Price] [money] NULL,
[Notes] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test_07052023] ([EMAILS SENT], [mmClientID], [Report Run Date], [Issue Name], [Issue Year], [Product], [Rate Card Name], [Customer ID], [Company/Database Name], [Ad Color], [Ad Size], [Sales Rep], [Quantity], [Net], [Gross], [Rate Card Price], [Notes]) VALUES (10615, 15, N'05-Jul-23', N'June', 2023, N'Marketing Manager', N'MKM Email', 1229, N'TOTI Media Inc.', N'N/A', N'Website, Additional', N'Kayla Walsh', N'', 0.0000, 0.0000, 99.0000, N'https://www.bonitaesteromagazine.com')
GO
INSERT [dbo].[test_07052023] ([EMAILS SENT], [mmClientID], [Report Run Date], [Issue Name], [Issue Year], [Product], [Rate Card Name], [Customer ID], [Company/Database Name], [Ad Color], [Ad Size], [Sales Rep], [Quantity], [Net], [Gross], [Rate Card Price], [Notes]) VALUES (10615, 15, N'05-Jul-23', N'June', 2023, N'Marketing Manager', N'MKM Email', 1229, N'TOTI Media Inc.', N'N/A', N'Website, Additional', N'Kayla Walsh', N'', 0.0000, 0.0000, 99.0000, N'https://www.capecorallivingmagazine.com')
GO
INSERT [dbo].[test_07052023] ([EMAILS SENT], [mmClientID], [Report Run Date], [Issue Name], [Issue Year], [Product], [Rate Card Name], [Customer ID], [Company/Database Name], [Ad Color], [Ad Size], [Sales Rep], [Quantity], [Net], [Gross], [Rate Card Price], [Notes]) VALUES (10615, 15, N'05-Jul-23', N'June', 2023, N'Marketing Manager', N'MKM Email', 1229, N'TOTI Media Inc.', N'N/A', N'Website, Additional', N'Kayla Walsh', N'', 0.0000, 0.0000, 99.0000, N'https://www.gulfmainmagazine.com')
GO
INSERT [dbo].[test_07052023] ([EMAILS SENT], [mmClientID], [Report Run Date], [Issue Name], [Issue Year], [Product], [Rate Card Name], [Customer ID], [Company/Database Name], [Ad Color], [Ad Size], [Sales Rep], [Quantity], [Net], [Gross], [Rate Card Price], [Notes]) VALUES (10615, 15, N'05-Jul-23', N'June', 2023, N'Marketing Manager', N'MKM Email', 1229, N'TOTI Media Inc.', N'N/A', N'Website, Additional', N'Kayla Walsh', N'', 0.0000, 0.0000, 99.0000, N'https://www.rswliving.com')
GO
INSERT [dbo].[test_07052023] ([EMAILS SENT], [mmClientID], [Report Run Date], [Issue Name], [Issue Year], [Product], [Rate Card Name], [Customer ID], [Company/Database Name], [Ad Color], [Ad Size], [Sales Rep], [Quantity], [Net], [Gross], [Rate Card Price], [Notes]) VALUES (10615, 15, N'05-Jul-23', N'June', 2023, N'Marketing Manager', N'MKM Email', 1229, N'TOTI Media Inc.', N'N/A', N'Website, Additional', N'Kayla Walsh', N'', 0.0000, 0.0000, 99.0000, N'https://www.timesoftheislands.com')
GO
INSERT [dbo].[test_07052023] ([EMAILS SENT], [mmClientID], [Report Run Date], [Issue Name], [Issue Year], [Product], [Rate Card Name], [Customer ID], [Company/Database Name], [Ad Color], [Ad Size], [Sales Rep], [Quantity], [Net], [Gross], [Rate Card Price], [Notes]) VALUES (10615, 15, N'05-Jul-23', N'June', 2023, N'Marketing Manager', N'MKM Email', 1229, N'TOTI Media Inc.', N'N/A', N'Website, Additional', N'Kayla Walsh', N'', 0.0000, 0.0000, 99.0000, N'https://www.toti.com')
GO
INSERT [dbo].[test_07052023] ([EMAILS SENT], [mmClientID], [Report Run Date], [Issue Name], [Issue Year], [Product], [Rate Card Name], [Customer ID], [Company/Database Name], [Ad Color], [Ad Size], [Sales Rep], [Quantity], [Net], [Gross], [Rate Card Price], [Notes]) VALUES (10615, 15, N'05-Jul-23', N'June', 2023, N'Marketing Manager', N'MKM Email', 1229, N'TOTI Media Inc.', N'N/A', N'Website, Included', N'Kayla Walsh', N'', 0.0000, 0.0000, 0.0000, N'https://www.bonitaesteromagazine.com')
GO
INSERT [dbo].[test_07052023] ([EMAILS SENT], [mmClientID], [Report Run Date], [Issue Name], [Issue Year], [Product], [Rate Card Name], [Customer ID], [Company/Database Name], [Ad Color], [Ad Size], [Sales Rep], [Quantity], [Net], [Gross], [Rate Card Price], [Notes]) VALUES (10615, 15, N'05-Jul-23', N'June', 2023, N'Marketing Manager', N'MKM Email', 1229, N'TOTI Media Inc.', N'N/A', N'Website, Included', N'Kayla Walsh', N'', 0.0000, 0.0000, 0.0000, N'https://www.capecorallivingmagazine.com')
GO
INSERT [dbo].[test_07052023] ([EMAILS SENT], [mmClientID], [Report Run Date], [Issue Name], [Issue Year], [Product], [Rate Card Name], [Customer ID], [Company/Database Name], [Ad Color], [Ad Size], [Sales Rep], [Quantity], [Net], [Gross], [Rate Card Price], [Notes]) VALUES (10615, 15, N'05-Jul-23', N'June', 2023, N'Marketing Manager', N'MKM Email', 1229, N'TOTI Media Inc.', N'N/A', N'Website, Included', N'Kayla Walsh', N'', 0.0000, 0.0000, 0.0000, N'https://www.gulfmainmagazine.com')
GO
INSERT [dbo].[test_07052023] ([EMAILS SENT], [mmClientID], [Report Run Date], [Issue Name], [Issue Year], [Product], [Rate Card Name], [Customer ID], [Company/Database Name], [Ad Color], [Ad Size], [Sales Rep], [Quantity], [Net], [Gross], [Rate Card Price], [Notes]) VALUES (10615, 15, N'05-Jul-23', N'June', 2023, N'Marketing Manager', N'MKM Email', 1229, N'TOTI Media Inc.', N'N/A', N'Website, Included', N'Kayla Walsh', N'', 0.0000, 0.0000, 0.0000, N'https://www.rswliving.com')
GO
INSERT [dbo].[test_07052023] ([EMAILS SENT], [mmClientID], [Report Run Date], [Issue Name], [Issue Year], [Product], [Rate Card Name], [Customer ID], [Company/Database Name], [Ad Color], [Ad Size], [Sales Rep], [Quantity], [Net], [Gross], [Rate Card Price], [Notes]) VALUES (10615, 15, N'05-Jul-23', N'June', 2023, N'Marketing Manager', N'MKM Email', 1229, N'TOTI Media Inc.', N'N/A', N'Website, Included', N'Kayla Walsh', N'', 0.0000, 0.0000, 0.0000, N'https://www.timesoftheislands.com')
GO
INSERT [dbo].[test_07052023] ([EMAILS SENT], [mmClientID], [Report Run Date], [Issue Name], [Issue Year], [Product], [Rate Card Name], [Customer ID], [Company/Database Name], [Ad Color], [Ad Size], [Sales Rep], [Quantity], [Net], [Gross], [Rate Card Price], [Notes]) VALUES (10615, 15, N'05-Jul-23', N'June', 2023, N'Marketing Manager', N'MKM Email', 1229, N'TOTI Media Inc.', N'N/A', N'Website, Included', N'Kayla Walsh', N'', 0.0000, 0.0000, 0.0000, N'https://www.toti.com')
GO
select * from [test_07052023] -
Hi Please find the expected output here:
Its little urgent please help.
Thanks
Bhanu
-
Use ROW_NUMBER() OVER (PARTITION BY<field list> ORDER BY <field>) where the columns in the PARTITION BY section are the columns you would use to determine uniqueness.
Standard way to do this is to use a common table expression, like this:
use tempdb;
go
CREATE TABLE SomeData (
FirstName VARCHAR(15) NOT NULL,
LastName VARCHAR(15) NOT NULL,
PhoneNumber CHAR(10)
);
GO
INSERT INTO SomeData VALUES
('Homer', 'Simpson', '4431234567'),
('Marge', 'Simpson', '4431234567'),
('Bart', 'Simpson', '5555555555'),
('Homer', 'Simpson', '4431234567');
with ctePhones (FirstName, LastName, Phone, DupeNo)
AS (
SELECT FirstName,
LastName,
PhoneNumber,
DupeNo = ROW_NUMBER() OVER
(PARTITION BY FirstName, LastName, PhoneNumber
ORDER BY FirstName, LastName, PhoneNumber)
FROM SomeData)
SELECT *
FROM ctePhones
WHERE DupeNo = 1; -
You examples are not duplicate rows. The fact that the Notes column is different in each row makes each row unique.
Your expected results removes all of the rows where the value of the "Ad Size" column is "Website, Included" except when the Notes is 'https://www.toti.com".
In order to utilize the logic in the above query to remove what you consider to be dupes, you are going to need to provide the columns, or combinations of columns and probably a value for these to filter on.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
any update?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply