Skip to Content
0
Former Member
Jan 26, 2011 at 09:11 AM

Q39 Multiple lines against each customer code with invoiced values

24 Views

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