Skip to Content
0
Former Member
Feb 22, 2011 at 10:28 AM

Q48: How to report values related only to RDR1.OpenQty

42 Views

This report works perfectly except where a line has been part shipped i.e. sales order quantity was for 200 x £10 but even though 10 have been shipped and invoiced this report still gives the value of £2,000 - how can I get this to report only £1,900..........???

SELECT

t0.cardcode as 'Customer Code',

t3.slpname as 'Sales Person',

--sum(t1.linetotal) as 'Sales Value',

--t0.discsum as 'Discount',

sum(t1.linetotal-t0.discsum) as 'Sales Value'

FROM

ordr t0 inner join rdr1 t1 on t0.docentry = t1.docentry

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

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

WHERE

t0.docduedate between and and

t1.linestatus = 'O' and

isnull (t0.u_forecast,'N') !='Y'

GROUP BY

t0.cardcode,

t3.slpname,

t0.discsum

ORDER BY t0.cardcode

As always, any help greatly appreciated............

Thanks,

Robin