Removing dupes... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Removing dupes...

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-01-03 : 09:52:11

Hi I need to remove same value positive and negative dupes (where they equate to 0)

I.e
Supplier_no, Invoice_Date, Invoice_Amount
123, 10/10/05, -10
123, 10/10/05, 10
123, 10/10/05, 200
123, 10/10/05, 200

So, the match on Supplier_no, Invoice_date and Invoice_Amount should be removed if the Invoice_Amount cancels each other out. Therefore the table should only display the below:

Supplier_no, Invoice_Date, Invoice_Amount
123, 10/10/05, 200
123, 10/10/05, 200

As these valus are both positive they can stay. If the values are negative and positive and result in 0 then they need to be removed.

Thanks in advance!!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-03 : 11:36:30

Are you saying you want to physically delete those rows from the table, or that when you are returning results in a SELECT you want to suppress certain rows?

Either way, your table needs a primary key to do this, otherwise there is no way to tell what data is valid and what should be deleted and what shouldn't. Does it have a PK?

Or, do you just want to return total amounts per day in a SELECT:

select supplier_no, Invoice_date, sum(Invoice_Amount) as TotalAmount
from YourTable
group by supplier_no, Invoice_Date

???

- Jeff

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-01-03 : 11:44:09

Physically delete these rows from a table.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-03 : 12:02:11

OK, then, what is the tables primary key?

and if your data is like this:

123, 10/10/05, -10
123, 10/10/05, 10
123, 10/10/05, -10

Which ones get deleted?

Or if it is like this:

123, 10/10/05, -10
123, 10/10/05, 200
123, 10/10/05, 10

Do the -10 and 10 still get deleted?

And finally, if it is like this:

123, 10/10/05, -10
123, 10/10/05, 5
123, 10/10/05, 5

Do we do nothing, or do those 3 rows get deleted (since they add up to zero) ?

- Jeff

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-01-03 : 12:33:09

OK, then, what is the tables primary key?
--Currently the table has no primary key (although I could create a numeric identity Primary Key

and if your data is like this:

123, 10/10/05, -10
123, 10/10/05, 10
123, 10/10/05, -10

Which ones get deleted?
-- one -10 and one 10, leaving one -10

Or if it is like this:

123, 10/10/05, -10
123, 10/10/05, 200
123, 10/10/05, 10

Do the -10 and 10 still get deleted?
--Yes

And finally, if it is like this:

123, 10/10/05, -10
123, 10/10/05, 5
123, 10/10/05, 5

Do we do nothing, or do those 3 rows get deleted (since they add up to zero) ?
--We can highlight these, but not delete them.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 13:47:41

Take a look at this

-- prepare sample data
declare	@t table (rowid int identity(1, 1), supplier int, invoice datetime, amount int)

insert	@t
select	123, '10/10/05', -10 union all
select	123, '10/10/05', 10 union all
select	123, '10/10/05', -10 union all
select	123, '10/10/05', -10 union all
select	123, '10/10/05', 200 union all
select	123, '10/10/05', 0 union all
select	123, '10/10/05', 10 union all
select	123, '10/10/05', -10 union all
select	123, '10/10/05', 5 union all
select	123, '10/10/05', 5

select * from @t

-- remove pairs
delete
from	@t
where	amount = 0

declare	@currentrowid int,
	@supplier int,
	@amount int,
	@invoice datetime,
	@rowid int

select	@currentrowid = 1

while @currentrowid <= (select max(rowid) from @t)
	begin
		select	@supplier = supplier,
			@invoice = invoice,
			@amount = amount
		from	@t
		where	rowid = @currentrowid

		select	@rowid = min(rowid)
		from	@t
		where	supplier = @supplier
			and invoice = @invoice
			and amount = -@amount

		if @rowid is not null
			delete
			from	@t
			where	rowid in (@rowid, @currentrowid)

		select	@currentrowid = min(rowid),
			@rowid = null
		from	@t
		where	rowid > @currentrowid
	end

-- show the result
select		supplier,
		invoice,
		amount
from		@t
order by	supplier,
		invoice,
		amount

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-03 : 14:12:10

Peso -- without a PK, this doesn't help much since there is no way to delete the rows from the table short of using ADO or something and manually stepping through the rows.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 14:44:06

I am sorry to disappoint you, Jeff.
There is a way to do this without Primary Key.

-- prepare sample data
declare	@t table (supplier int, invoice datetime, amount int)

insert	@t
select	123, '10/10/05', -10 union all
select	123, '10/10/05', 10 union all
select	123, '10/10/05', -10 union all
select	123, '10/10/05', -10 union all
select	123, '10/10/05', 200 union all
select	123, '10/10/05', 0 union all
select	123, '10/10/05', 10 union all
select	123, '10/10/05', -10 union all
select	123, '10/10/05', 5 union all
select	123, '10/10/05', 5

select		supplier,
		invoice,
		amount
from		@t
order by	supplier,
		invoice,
		amount

-- remove pairs
delete
from	@t
where	amount = 0

declare	@supplier int,
	@amount int,
	@invoice datetime,
	@nextsupplier int,
	@nextamount int,
	@nextinvoice datetime

set rowcount 1

select		@supplier = supplier,
		@invoice = invoice,
		@amount = amount
from		@t
order by	supplier,
		invoice,
		amount

while @supplier is not null
	begin
		delete
		from	@t
		where	supplier = @supplier
			and invoice = @invoice
			and amount = -@amount

		if @@rowcount > 0
			begin
				delete
				from	@t
				where	supplier = @supplier
					and invoice = @invoice
					and amount = @amount

				select		@supplier = supplier,
						@invoice = invoice,
						@amount = amount
				from		@t
				order by	supplier,
						invoice,
						amount
			end
		else
			begin
				select		@nextsupplier = supplier,
						@nextinvoice = invoice,
						@nextamount = amount
				from		@t
				where		supplier = @supplier
						and invoice = @invoice
						and amount > @amount
				order by	supplier,
						invoice,
						amount

				if @nextsupplier is null
					select		@nextsupplier = supplier,
							@nextinvoice = invoice,
							@nextamount = amount
					from		@t
					where		supplier = @supplier
							and invoice > @invoice
					order by	supplier,
							invoice,
							amount

				if @nextsupplier is null
					select		@nextsupplier = supplier,
							@nextinvoice = invoice,
							@nextamount = amount
					from		@t
					where		supplier > @supplier
					order by	supplier,
							invoice,
							amount

				if @@rowcount > 0
					select	@supplier = @nextsupplier,
						@invoice = @nextinvoice,
						@amount = @nextamount
				else
					select	@supplier = null
			end
	end

set rowcount 0

-- show the result
select		supplier,
		invoice,
		amount
from		@t
order by	supplier,
		invoice,
		amount

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-03 : 14:55:38

Hi Peso -- I'm not really disapointed, there's always ways to do it (i.e., as I mentioned with a cursor) but the idea is that if you have a table w/o a primary key, then as someone once said (Celko?) you don't have a table; there's no way to objectively analyze and work with the data.

Anyway, any solution proposed is worthless until the questions I posed earlier are answered. I'm sure there's other scenarios that I didn't think of that will pop up as well.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources