Hi,
I'll start off with im rubbish with this query writer and not asking for much (I don't think).
I want to be able to run a report that shows me
Between Dates
U_SORT01 (A UDF I have)
Invoice DocNum
Invoice DocTotal
Credit DocNum
Credit DocTotal
I then would like to show Invoice Doc total - Credits by U_SORT01.
The reports I keep building shows me some false lines.
So far I can build the 2 reports on their own please see below
Invoice
SELECT T0.[U_SORT01], SUM(T1.[DocTotal]) AS Invoice
FROM OCRD T0 INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode WHERE T1.[DocDate] between '[%0]' and '[%1]' and T1.[DocStatus] = 'o'
GROUP BY T0.[U_SORT01]
Credit report
SELECT T0.[U_SORT01], SUM(T1.[DocTotal]) AS Credit
FROM OCRD T0 INNER JOIN ORIN T1 ON T0.CardCode = T1.CardCode WHERE T1.[DocDate] between '[%0]' and '[%1]' and T1.[DocStatus] = 'o'
GROUP BY T0.[U_SORT01]
All i need to do now is get the credits to minus from the invoice but making sure that they minus from the correct place.
EG
SORT 001 invoice needs to have SORT 001 Credits minused.
Any help would be appreciated.
Thanks
Joe