Matching within a SQL table

[jtigges]

I have a SQL Server 2008 table called invoices. I’m trying to use an UPDATE SQL command to perform the following column compares within a single invoice table. Example below. Tried numerous attempts, but do not get the results as expected in the offset column. Any help would be much appreciated – thanks Jeff.

Challenge 1:
UPDATE the below invoice table to identify the offsetting credits and SET offset=’x’ WHERE:
Vendor number is equal
Invoice number is equal
Invoice amount is equal
Debit credit code is not equal

Vendor Invoice Invoice invoice debit offset
Number Number date amount credit
123 1 1/10/08 $50.00 D
123 2 1/10/08 $10.00 D X
123 3 1/10/08 $30.00 D
123 2 1/10/08 $10.00 C X
33 IN1 2/1/08 $5.00 D
33 IN3 1/1/08 $4.00 D X
33 IN3 1/1/08 $4.00 C X
33 IN3 1/1/08 $4.00 D
33 IN2 6/5/08 $1.00 D

Challenge 2:
UPDATE the below invoice table to identify the offsetting credits and SET offset=’x’ WHERE:
Vendor number is equal
Invoice number is equal
Invoice amount is equal after *-1

Vendor Invoice Invoice invoice offset
Number Number date amount
123 1 1/10/08 $50.00
123 2 1/10/08 $10.00 X
123 3 1/10/08 $30.00
123 2 1/10/08 $-10.00 X
33 IN1 2/1/08 $5.00
33 IN3 1/1/08 $4.00 X
33 IN3 1/1/08 $-4.00 X
33 IN3 1/1/08 $4.00
33 IN2 6/5/08 $1.00

[ck9663]

You have these:

33 IN3 1/1/08 $4.00 D X
33 IN3 1/1/08 $4.00 C X
33 IN3 1/1/08 $4.00 D

The D-Record becomes duplicate. Do you have duplicate records on your table? Which one should it pick?

-- CK

[jtigges]

The second D record for 33 should not be picked. The prior C record was match with a single D creating the offsets.

Thanks Jeff

[ck9663]

Your duplicate records will give you a lot of inaccurate matching.

Which tells me that you might have a bigger problem that you actually have. Duplicate record is not always wrong but it could cause a lot of trouble, so you might want to look at that first.

In any case, without duplicate records, you can try the following code:

declare @yourtable table (VendorNumber int, InvoiceNumber varchar(5), InvoiceDate smalldatetime, InvoiceAmount money, DebitCredit varchar(1), Offset varchar(1))

insert into @yourtable values(123, '1', '1/10/08', '$50.00', 'D',null)
insert into @yourtable values(123, '2', '1/10/08', '$10.00', 'D',null) 
insert into @yourtable values(123, '3', '1/10/08', '$30.00', 'D',null)
insert into @yourtable values(123, '2', '1/10/08', '$10.00', 'C',null) 
insert into @yourtable values(33, 'IN1', '2/1/08', '$5.00', 'D',null)
insert into @yourtable values(33, 'IN3', '1/1/08', '$4.00', 'D',null) 
insert into @yourtable values(33, 'IN3', '1/1/08', '$4.00', 'C',null) 
insert into @yourtable values(33, 'IN2', '6/5/08', '$1.00', 'D',null)


update t 
set OffSet = 'X'
from @yourtable t
inner join @yourtable z on z.VendorNumber = t.VendorNumber and z.InvoiceNumber = t.InvoiceNumber and z.InvoiceDate = t.InvoiceDate and z.InvoiceAmount = t.InvoiceAmount 
where ((t.DebitCredit = 'D' and z.DebitCredit = 'C') or (t.DebitCredit = 'C' and z.DebitCredit = 'D'))

select * from @yourtable

Happy coding!

-- CK