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]
