Q39 Multiple lines against each customer code with invoiced values

[Former Member]

Hi All,

This is a different problem to my 'Q38' question (multiple lines against a customer code) but along the same lines, this time invoiced values are involved:

SELECT

t0.cardcode as 'Customer Code',

t0.cardname as 'Customer name',

t1.e_mail as 'Email',

t1.frozenfor as 'On Hold',

t2.slpname as 'Sales Person',

sum(t0.doctotal-t0.vatsum) as 'Invoiced Value',

t0.totalexpns

FROM

oinv t0

inner join ocrd t1 on t0.cardcode = t1.cardcode

inner join oslp t2 on t1.slpcode = t2.slpcode

WHERE

t0.docdate between [%1] and [%2]

GROUP BY

t0.cardcode,

t0.cardname,

t1.e_mail,

t1.frozenfor,

t2.slpname,

t0.totalexpns

HAVING

sum(t0.doctotal-t0.vatsum) > 0

ORDER BY

t2.slpname,

t0.cardcode

Thanks in advance for a solution...........

Robin

[Former Member · Accepted solution]

you try this :

SELECT

t0.cardcode as 'Customer Code',

t0.cardname as 'Customer name',

t1.e_mail as 'Email',

t1.frozenfor as 'On Hold',

t2.slpname as 'Sales Person',

sum(t0.doctotal-t0.vatsum) as 'Invoiced Value',

sum(t0.totalexpns) as totalexpns

FROM

oinv t0

inner join ocrd t1 on t0.cardcode = t1.cardcode

inner join oslp t2 on t1.slpcode = t2.slpcode

WHERE

t0.docdate between [%0] and [%1]

GROUP BY

t0.cardcode,

t0.cardname,

t1.e_mail,

t1.frozenfor,

t2.slpname

HAVING

sum(t0.doctotal-t0.vatsum) > 0

ORDER BY

t2.slpname,

t0.cardcode

regards,

H2

Edited by: Hoe Pham Huy on Jan 26, 2011 10:49 AM

[Former Member · Accepted solution]

Thank you my friend........works a treat!

[Former Member]

Hi Robin,

The above query has worked it had been the sum(t0.totalexpns) as totalexpns missing in your Select statement, because frieght included for customer was repeated in the another line the above sum(t0.totalexpns) as totalexpns sums up all the frieght invoiced and the no friegh invoice value.

Regards,

Rakesh N