Stef,
> Can somebody please have a look at this query and let me know how I can
> improve the performance (10 seconds execute time)
You are processing the Journal table 3 times, for no real benefit AFAICT.
BTW, what fields are indexed, in what combinations?
To your question, how does this work for you? (I would like to see the PLAN
for this statement)
SELECT
supplier, T.d120, T.d90,T.d60, T.d30, T.current_days, T.totaldue
FROM(
SELECT
s.suppid
sum(iif(ddate <= '2015/05/31', debitamount - creditamount, 0)) as D120,
sum(iif(ddate between '2015/06/01' and '2015/06/30', debitamount -
creditamount, 0)) as D90,
sum(iif(ddate between '2015/07/01' and '2015/07/31', debitamount -
creditamount, 0)) as d60,
sum(iif(ddate between '2015/08/01' and '2015/08/31', debitamount -
creditamount, 0)) as d30,
sum(iif(ddate between '2015/09/01' and '2015/09/30', debitamount -
creditamount, 0)) as current_days,
sum(debitamount - creditamount) as totaldue
from journal
where
accnr = 5995100
and suppid = 3
and ddate <= '2015/09/30'
) T
JOIN supplier s ON T.suppid = s.suppid
WHERE
T.TotalDue <>0
Sean
