on 12-09-2011 3:22 PM
Hi
SELECT DISTINCT T0.[CardName] as 'Party Name', T0.[DocNum] as 'Inv No', T0.[DocDate] as 'Date', (T0.[DocTotal]-T0.[VatSum]) as 'Basic', T0.[VatSum] as 'Taxes', T0.[DocTotal] as 'Inv Total', T1.U_O_Payment , (T0.DocTotal+T1.U_O_Payment) FROM OINV T0 INNER JOIN ORDR T1 ON T0.cardcode = T1.cardcode INNER JOIN OCRD T2 ON T0.cardcode = T2.cardcode
WHERE t0.docdate >=[%1] and t0.docdate <=[%2]
GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardName], T0.[DocTotal], T0.[VatSum], T1.U_O_Payment
It is not displaying information from those orders which are related to invoice wise. Isn't it is related to through doc number or base document no.
Thanks
Hi,
Try:
SELECT DISTINCT T0.CardName as 'Party Name', T0.DocNum as 'Inv No', T0.DocDate as 'Date', (T0.DocTotal-T0.VatSum) as 'Basic', T0.VatSum as 'Taxes', T0.DocTotal as 'Inv Total', MAX(T3.U_O_Payment ), (T0.DocTotal+MAX(T3.U_O_Payment))
FROM OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry=T0.DocEntry
INNER JOIN OCRD T2 ON T0.cardcode = T2.cardcode
LEFT JOIN ORDR T3 ON T3.DocEntry= T1.BaseEntry AND T1.BaseType= 17
WHERE t0.docdate >=[%1\] and t0.docdate <=[%2\]
GROUP BY T0.DocNum, T0.DocDate, T0.CardName, T0.DocTotal, T0.VatSum
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
102 | |
12 | |
11 | |
6 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.