By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

Matching within a SQL table

P: 2
I have a SQL Server 2008 table called invoices. Im 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
Oct 13 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Oct 13 '08 #2

P: 2
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
Oct 14 '08 #3

ck9663
Expert 2.5K+
P: 2,878
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:

Expand|Select|Wrap|Line Numbers
  1. declare @yourtable table (VendorNumber int, InvoiceNumber varchar(5), InvoiceDate smalldatetime, InvoiceAmount money, DebitCredit varchar(1), Offset varchar(1))
  2.  
  3. insert into @yourtable values(123, '1', '1/10/08', '$50.00', 'D',null)
  4. insert into @yourtable values(123, '2', '1/10/08', '$10.00', 'D',null) 
  5. insert into @yourtable values(123, '3', '1/10/08', '$30.00', 'D',null)
  6. insert into @yourtable values(123, '2', '1/10/08', '$10.00', 'C',null) 
  7. insert into @yourtable values(33, 'IN1', '2/1/08', '$5.00', 'D',null)
  8. insert into @yourtable values(33, 'IN3', '1/1/08', '$4.00', 'D',null) 
  9. insert into @yourtable values(33, 'IN3', '1/1/08', '$4.00', 'C',null) 
  10. insert into @yourtable values(33, 'IN2', '6/5/08', '$1.00', 'D',null)
  11.  
  12.  
  13. update t 
  14. set OffSet = 'X'
  15. from @yourtable t
  16. inner join @yourtable z on z.VendorNumber = t.VendorNumber and z.InvoiceNumber = t.InvoiceNumber and z.InvoiceDate = t.InvoiceDate and z.InvoiceAmount = t.InvoiceAmount 
  17. where ((t.DebitCredit = 'D' and z.DebitCredit = 'C') or (t.DebitCredit = 'C' and z.DebitCredit = 'D'))
  18.  
  19. select * from @yourtable
  20.  
  21.  
Happy coding!

-- CK
Oct 14 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.