SQL Code Duplicate Vouchers in Query

The issue that I keep getting is that the same voucher number is duplicated multiple times. 

I would think some of the conditions are not being joined there. Are you missing some join condition there?

to quickly resolve this, try add the Distinct clause.

such as:

SELECT DISTINCT   a.ACCOUNTNUM, CAST(a.TRANSDATE AS date) AS transdate, a.VOUCHER, a.INVOICE, a.CURRENCYCODE, a.TXT,....

Select allOpen in new window

If it's still "duplicate", then it would probably the data issue.

Hi Ryan,

Ok adding DISTINCT:

 SELECT  DISTINCT 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.VOUCHER = 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, a.VOUCHER, a.INVOICE, a.CURRENCYCODE, a.TXT,
  a.DUEDATE, a.AMOUNTCUR, a.SETTLEAMOUNTCUR, a.SETTLEAMOUNTMST, a.AMOUNTMST
 , a.LASTSETTLEVOUCHER, f.INVOICEID , a.TRANSTYPE

Select allOpen in new window

I get the error Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

The order is correct from I can tell, please advise.

Thank you. 

Hi Ryan,

Cheers for that I'm pretty sure it's  data issue there are multiple columns where the transdate is NULL

Thank you.