Hi Set



Wow your query runs like a bat out of hell J 0.16 Milliseconds !! and it
easier to maintain as you say.



The only issue is that it somehow multiplies the totals by 6 on all results
as compared mine?



i.e. mine will return an invoice total of say 18500.00 and yours will return
111000.00



This is across all the results any idea why?



Stef



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: 22 June 2015 09:29 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Firebird migration to increase perfomances

>Select distinct trim(surname)as surname, trim(name)as name, trxdate,

source, job_number, creditnr, sourceid, serviceadvisOR, (credvat)as credvat,
(invoiceVAT) AS VAT,

> (invoiceINC)AS invoicetotalINC, (invoiceINC-invoicevat+credvat)as

invoicetotal, (credinc+creditnote-(invoiceINCRedo-invoiceVATRedo)) as
credits,

> (invoiceINC-invoicevat+(credinc))+(creditnote)-(invoiceINCRedo)as nett,

(invcost) as cost,

>

((invoiceINC-invoicevat+(credinc))+(creditnote))-(invcost)-(invoiceINCRedo)
as gp,

>

((((invoiceINC-invoicevat+(credinc))+(creditnote))-(invcost)-(invoiceINCRedo
)))/NULLIF(((invoiceINC-invoicevat+(credinc))+(creditnote)),0)*100 as
gppersent

>from (select (jdate)as trxdate, source, sourceid,(source2) as

creditnr,i.job_number,

> (select name||' '||surname from staff where staff_id = j.serviceadvisOR)as

serviceadvisOR,

> (select name||' '||surname from customer where cid = j.cid)as surname,
> (select ca.name from cashcustomer ca
> where ca.invnr = j.source AND ca.jobnr = i.job_number) as name,
> (Select COALESCE(sum(debitamount),0 ) from journal
> where accnr = '3993100' AND source = j.source AND jtype =1 AND jdate =

j.jdate )as invoiceINC,

> (Select COALESCE(sum(creditamount-debitamount),0 ) from journal
> where accnr = '5995300' AND source = j.source AND jtype =1 AND jdate =

j.jdate )as invoiceVAT,

> (Select COALESCE(sum(creditamount),0 ) from journal where accnr =

'3993100' AND source = j.source AND jtype =1 AND jdate = j.jdate AND module
= 77) as invoiceINCRedo,

> (Select COALESCE(sum(debitamount-creditamount),0 ) from journal
> where accnr = '5995300' AND source = j.source AND jtype =1 AND jdate =

j.jdate AND module = 77)as invoiceVATRedo,

> (Select COALESCE(sum(creditamount-debitamount),0 ) from journal
> where accnr = '5995300' AND (module = 77 OR MODULE = 66 )AND source =

j.source AND source2 = j.source2 AND jtype =1 AND jdate = j.jdate) as
credVAT,

> (Select COALESCE(sum(creditamount-debitamount),0 ) from journal
> where accnr = '5995300' AND (module = 77 OR MODULE = 66 ) AND source =

j.source AND source2 = j.source2 AND jtype =4 AND jdate = j.jdate) as
creditnoteVAT,

> (Select COALESCE(0-sum(creditamount-debitamount),0 ) from journal
> where accnr = '1501500' AND source = j.source AND jtype =1 AND jdate =

j.jdate)as credinc,

> (Select COALESCE(0-sum(debitamount-creditamount),0 ) from journal
> where accnr = '1501500' AND source = j.source AND jtype =4 AND jdate =

j.jdate)as creditnote,

> (Select (COALESCE(SUM(debitamount-CREDitamount),0)) from journal
> where accnr like '6%' AND source = j.source AND jtype = 1 AND jdate =

j.jdate)as invcost

> from journal j, invoices i
> where j.source not like 'STOCK UNIT%'
> AND (j.jdate >= '2015/06/01'
> AND j.jDATE <= '2015/06/22')
> AND j.source not like 'DEPOSIT%'
> AND j.cid <> 0
> AND i.inv_nr = j.source
> AND j.module <> 2
> AND (j.source2 not like 'S/D%'
> AND j.source2 not like 'P/D%'
> OR j.source2 is null)
> AND j.description not like 'SPLIT INV%'
> AND (j.jtype = 1 OR j.jtype = 4)
>-- Below is where my problem lies
>-- Why can I not use the result fields
>-- i.e (invoiceINC-invoicevat+credvat) <> 0
>-- instead of the sql below for my <> 0 condition??
> AND (
> (Select COALESCE(sum(debitamount),0 )
> from journal where accnr = '3993100' AND source = j.source AND jtype = 1

AND jdate = j.jdate )

> -
> (Select COALESCE(sum(creditamount-debitamount),0 )
> from journal where accnr = '5995300' AND source = j.source AND jtype = 1

AND jdate = j.jdate )

> +
> (Select COALESCE(sum(creditamount-debitamount),0 )
> from journal where accnr = '5995300'
> AND (module = 77 OR MODULE = 66 )AND source = j.source AND source2 =

j.source2 AND jtype =1 AND jdate = j.jdate)

> ) <> 0
> )
>Order by 1,3,4

Don't know how much quicker it will be, but with Fb 2.5 you could change
your query to something like:

with tmp(surname, name, trxdate, source, job_number, creditnr, sourceid,
serviceadvisOR, credVAT, VAT,invoicetotalINC, credinc, creditnote,
invoiceINCRedo, invoiceVATRedo, cost) as
(select trim(c.name||' '||c.surname), trim(ca.name), j.jdate, j.source,
i.job_number, j.source2, j.sourceid, s.name||' '||s.surname,
sum(iif(j2.accnr = '5995300' and j2.module in (66, 77) and j2source2 =
j2.source2 and j2.jtype = 1, coalesce(j2.creditamount, 0) -
coalesce(j2.debitamount, 0), 0)),
sum(iif(j2.accnr = '5995300' and j2.jtype = 1, coalesce(j2.creditamount, 0)
- coalesce(j2.debitamount, 0), 0)),
sum(iif(j2.accnr = '3993100' and j2.jtype = 1, j2.debitamount, 0)),
sum(iif(j2.accnr = '1501500' and j2.jtype = 1, coalesce(j2.debitamount,
0)-coalesce(j2.creditamount, 0), 0)),
sum(iif(j2.accnr = '1501500' and j2.jtype = 4, coalesce(j2.creditamount,
0)-coalesce(j2.debitamount, 0), 0)),
sum(iif(j2.accnr = '3993100' and j2.jtype = 1 and j2.module = 77,
j2.creditamount, 0)),
sum(iif(j2.accnr = '5995300' and j2.jtype = 1 and j2.module = 77,
coalesce(j2.debitamount, 0)-coalesce(j2.creditamount, 0), 0)),
sum(iif(j2.accnr starting '6' and j2.jtype = 1, coalesce(j2.debitamount,
0)-coalesce(j2.creditamount, 0), 0))
from journal j
join invoices i on j.source = i.inv_nr
join journal j2 on j.source = j2.source
and j.jdate = j2.jdate
and j2.jtype in (1, 4)
and (j2.accnr in ('1501500', '3993100', '5995300')
or j2.accnr starting '6')
left join customer c on j.cid = c.cid
left join cashcustomer ca on j.source = ca.invnr
and i.job_number = ca.jobnr
left join staff s on j.serviceadvisOR = s.staff_id
where j.source not starting 'STOCK UNIT'
and j.jdate between '2015/06/01' AND '2015/06/22'
and j.source not starting 'DEPOSIT'
and j.cid <> 0
and j.module <> 2
and ((j.source2 not starting 'S/D'
and j.source2 not starting 'P/D')
or j.source2 is null)
and j.description not starting 'SPLIT INV'
and j.jtype in (1, 4)
group by 1, 2, 3, 4, 5, 6, 7, 8)

select surname, name, trxdate, source, job_number, creditnr, sourceid,
serviceadvisOR, credVAT, VAT, invoicetotalINC, invoicetotalINC-VAT+credvat
as invoicetotal,
credinc+creditnote-invoiceINCRedo+invoiceVATRedo credits,
invoicetotalINC-VAT+credinc+creditnote-invoiceINCRedo as nett, cost,
invoicetotalINC-VAT+credinc+creditnote-cost-invoiceINCRedo as gp,
invoicetotalINC-VAT+credinc+creditnote-cost-invoiceINCRedo/NULLIF(invoicetot
alINC-VAT+credinc+creditnote, 0)*100 as gppersent
from tmp
where invoiceINC-invoicevat+credvat <> 0

This query is slightly different from yours since I've used COALESCE
wherever you have both debitamount and creditamount within the same sum - I
suspect your query will eliminate rows where only one of those fields are
filled in.

The main reason for me recommending you to try this query is not related to
performance, but that I find it more easily readable and hence maintainable.
Still, I'd expect it to be somewhat quicker than your original query (it's
always nice to see comparisons between queries, so please report back).

Note, there's likely to be a few mistakes, Notepad is one of my favorite
tools for writing SQL, whereas it is useless for syntax or semantics
checking.

HTH,
Set





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