troubleshooting Question

SQL Code Duplicate Vouchers in Query

Avatar of Zack
ZackFlag for Australia asked on
Microsoft SQL ServerSQL
4 Comments1 Solution6 ViewsLast Modified:
Hi EE,

I have the following query:

 SELECT    a.ACCOUNTNUM, CAST(a.TRANSDATE AS date) AS transdate, a.VOUCHER, a.INVOICE, a.CURRENCYCODE, a.TXT,
  CAST(a.DUEDATE AS date) AS duedate, a.AMOUNTCUR, a.SETTLEAMOUNTCUR, a.SETTLEAMOUNTMST, a.SETTLEAMOUNTMST AS ORIGSETTELEMST, a.AMOUNTMST
 , a.LASTSETTLEVOUCHER, f.INVOICEID AS lastsettleinvoice, a.TRANSTYPE
 , EXCHADJUSTMENT
 , CASE WHEN A.AMOUNTMST < 0 THEN A.AMOUNTMST ELSE 0 END AS MSTCREDIT
 , CASE WHEN A.AMOUNTCUR < 0 THEN A.AMOUNTCUR ELSE 0 END AS CURCREDIT  
 , CASE WHEN A.AMOUNTMST > 0 THEN A.AMOUNTMST ELSE 0 END AS MSTDEBET
 , CASE WHEN A.AMOUNTCUR > 0 THEN A.AMOUNTCUR ELSE 0 END AS CURDEBET
 , A.AMOUNTMST - A.SETTLEAMOUNTMST + A.EXCHADJUSTMENT  Balance
FROM         VENDTRANS AS a LEFT OUTER JOIN
                      VENDTABLE AS b ON a.ACCOUNTNUM = b.ACCOUNTNUM LEFT OUTER JOIN
                      --DIRPARTYTABLE AS c ON b.PARTY = c.RECID LEFT OUTER JOIN
                     -- VENDTABLE AS d ON a.ACCOUNTNUM = d.ACCOUNTNUM LEFT OUTER JOIN
                      VENDINVOICEJOUR AS f ON a.LASTSETTLEVOUCHER = f.LEDGERVOUCHER AND f.INVOICEACCOUNT = a.ACCOUNTNUM AND f.DATAAREAID = a.DATAAREAID AND f.INVOICEACCOUNT= CONVERT(VARCHAR, a.transdate, 103)
ORDER BY a.ACCOUNTNUM, a.TRANSDATE

The issue that I keep getting is that the same voucher number is duplicated multiple times.
How would I go about troubleshooting this, any assistance is welcome.

Thank you
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros