Skip to Content
0
Former Member
Nov 23, 2009 at 11:54 AM

Query giving wrong result

16 Views

Hi

Could anybody tell me why this query is giving the wrong result? The column 'Spend for Selected Period' is showing as far too much (ie exactly 11 times too much!!!) when totalling the 37 invoices involved

SELECT T0.[CardCode], T0.[CardName],T0.[MailCity] AS 'Town', T2.[SlpName] AS 'Rep',

SUM(T1.DocTotal - T1.VatSum) AS 'Spend for Selected Period', MAX(T3.CreateDate) AS 'Last Visit Date'

FROM OCRD T0 left JOIN OINV T1 ON T0.CardCode = T1.CardCode LEFT JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode LEFT JOIN OSCL T3 ON T0.CardCode = T3.Customer

where T0.[CardCode] = 'wyk027/34' AND T1.DocDate BETWEEN '20090101' AND '20091231'

GROUP BY T0.[CardCode], T0.[CardName],T0.[MailCity], T2.[SlpName]

Thanks

Steve