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
ASKER
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
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
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:
Open in new window
If it's still "duplicate", then it would probably the data issue.