Experts,
This commission query showing Invoices and Payments show the payment twice if there is 2 invoices linked to this payment. Is there any way to correct this?
SELECT T2.[DocNum], T2.[DocDate], T2.[DocDueDate], T2.[CardCode], T2.[CardName], T2.[DocTotal], T0.[DocNum]
as 'Payment Document', T0.[DocDate] As 'Payment Date', T0.[DocTotal] as 'Paid Amount', T3.[SlpName], T3.[Commission]
as 'Commission Persentage', SUM(T0.[DocTotal]*T3.[Commission]/100)
FROM ORCT T0 INNER JOIN RCT2 T1 ON T0.DocNum = T1.DocNum
INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry
INNER JOIN OSLP T3 ON T2.SlpCode = T3.SlpCode
WHERE T0.[DocDate] >=[%1] and t0.docdate <= [%2]
Group by T2.[DocNum], T2.[DocDate], T2.[DocDueDate], T2.[CardCode], T2.[CardName], T2.[DocTotal], T0.[DocNum] ,
T0.[DocDate] , T0.[DocTotal] , T3.[SlpName], T3.[Commission]
Thanks,
Marli