Expert 2GB |
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
| |