I require a query that will list for each day the total of Invoices (sum(OINV.Doctotal) and Credits (sum(ORIN.Doctotal)) and the difference between the 2.
Could I join on the docdate or would a nested select be better?
E.g. I tried to outer join on docdate but this gives incorrect totals
SELECT isnull(t0.DocDate,t1.docdate) as 'Date', sum(t0.DocTotal) as'Daily Invoices',sum(t1.doctotal) as 'Daily Credits', isnull(SUM(t0.doctotal),0)-isnull(SUM(t1.doctotal),0) as 'Total'
FROM OINV T0 full outer join ORIN t1 on t1.DocDate=t0.DocDate
where t0.DocDate>= [%0] and t0.DocDate<=[%1]
group by t0.DocDate,t1.DocDate
order by t0.DocDate
Thank you for your help.