Skip to Content
0
Former Member
Jun 25, 2012 at 09:42 PM

Commission query show payment duplicates

49 Views

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