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
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