Skip to Content
0
Former Member
Oct 24, 2012 at 10:20 AM

Query Help - OINV and ORIN

188 Views

Hello experts,

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.

Naomi