Query giving wrong result

[Former Member]

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

[Former Member]

Hi Madhan

Your query gives the error msg "must specify table to select from"

Again I think this has to do with the nested SELECT statement

Regards

Steve

[Former Member]

Hi,

Try this,

SELECT T0.CardCode, T0.CardName,T0.MailCity AS 'Town', T2.SlpName AS 'Rep',
SUM(T1.DocTotal - T1.VatSum) AS 'Spend for Selected Period', 
(SELECT MAX(T3.CreateDate) FROM OSCL T3 
WHERE T0.CardCode = T3.Customer) AS 'Last Visit Date'
FROM OCRD T0 
INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode 
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
WHERE 
T0.CardCode = '[%0]' 
AND 
T1.DocDate BETWEEN '[%1]' AND '[%2]'
GROUP BY 
T0.CardCode, T0.CardName,T0.MailCity, T2.SlpName

Regards,

Madhan.

[Former Member]

Many thanks Maya that has worked

Just one other thing ............. I think because of the sub-select statement in your code I cannot now use the ..........

BETWEEN [%0] AND [%1] 

syntax for the date ranges.

Do you know how to overcome this?

Regards

Steve

[Former Member]

Try T1.DocDate BETWEEN '[%1\]' AND '[%2\]'. It should work at least in our environment.

[former_member321384]

Hi ,

It seems like it is duplicated by the number of service calls.

Try this one:

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

SUM(T1.DocTotal - T1.VatSum) AS 'Spend for Selected Period', (select MAX(T3.CreateDate) from OSCL T3 where T0.CardCode = T3.Customer) AS 'Last Visit Date'

FROM OCRD T0 inner JOIN OINV T1 ON T0.CardCode = T1.CardCode inner JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

where T0.CardCode = '10001' AND T1.DocDate BETWEEN '20090101' AND '20091231'

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

Best regards,

Maya