Hi All



Can somebody please have a look at this query and let me know how I can
improve the performance (10 seconds execute time)



with DateSource(suppid, ddate, source2) as

(select distinct suppid, ddate, source2

from journal

where accnr = 5995100

and suppid = 3

),

tmp(suppid, source, ddate, paid, invoicetotal) as

(select d.suppid, d.source2, d.ddate, sum(debitamount),
sum(creditamount)

from journal j

join DateSource d on j.ddate = d.ddate and j.source2 = d.source2
and j.suppid = d.suppid

where j.accnr = 5995100

and d.suppid = 3

group by 1,2 ,3),




tmp2(account_nr, supplier, d120, d90,d60,d30,current_days, Totaldue) as


(select distinct s.suppid, trim(s.supplier),

sum(iif(ddate <= '2015/05/31', invoicetotal - paid, 0)) as D120,


sum(iif(ddate between '2015/06/01' and '2015/06/30', invoicetotal -
paid, 0)) as D90,

sum(iif(ddate between '2015/07/01' and '2015/07/31', invoicetotal -
paid, 0)) as d60 ,

sum(iif(ddate between '2015/08/01' and '2015/08/31', invoicetotal -
paid, 0)) as d30 ,

sum(iif(ddate between '2015/09/01' and '2015/09/30', invoicetotal -
paid, 0)) as current_days ,

sum(iif(ddate <= '2015/09/30', invoicetotal - paid, 0)) as totaldue





from tmp t


join supplier s on t.suppid = s.suppid


group by 1,2


having sum(iif(ddate <= '2015/09/30', invoicetotal - paid, 0)) <> 0)


select account_nr, supplier, d120, d90,d60,d30,current_days, totaldue


from tmp2


order by 2,1





Regards



Stef van der Merwe



[Non-text portions of this message have been removed]