Problema con un SUM
Publicado por jperet (2 intervenciones) el 24/01/2007 17:23:29
Fijaos en esta consulta SQL:
SELECT DISTINCT T3.SlpName AS [Representante], T5.Name AS [Provincia], T1.CardCode, T1.CardName, SUM(ISNULL(T0.DocTotal,0)) AS [TOTAL]
FROM ODLN T0
RIGHT JOIN OCRD T1 ON T0.CardCode=T1.CardCode AND T0.DocDate BETWEEN '[%1]' AND '[%2]'
LEFT JOIN ( SELECT CardCode, State, City FROM CRD1 GROUP BY City,State, CardCode ) T2 ON T1.CardCode=T2.CardCode
LEFT JOIN ( SELECT SlpCode, SlpName FROM OSLP GROUP BY SlpCode,SlpName ) T3 ON T1.SlpCode=T3.SlpCode
LEFT JOIN ( SELECT GroupCode, GroupName FROM OCRG GROUP BY GroupCode, GroupName ) T4 ON T1.GroupCode=T4.GroupCode
INNER JOIN ( SELECT Code, Name FROM OCST GROUP BY Code,Name ) T5 ON T2.State=T5.Code
WHERE
(T5.name='MADRID' OR T5.name='AVILA' OR T5.name='GUADALAJARA' OR T5.name='SEGOVIA' OR T5.name='TOLEDO') AND (t4.GroupName='MOTO 44' or t4.GroupName='MOTO 50' or t4.GroupName='MOTO 37' or t4.GroupName='MOTO 30' or t4.GroupName='MOTO 40')
AND (T1.CardCode BETWEEN 'C000000' AND 'C999999')
GROUP BY T3.SlpName, T1.CardCode, T1.CardName, T4.GroupName, T5.Name, T2.State
ORDER BY T5.Name
------------------------------------------------------
La tabla ODLN es la de albaranes, donde hay varios campos, entre ellos el código y el nombre del cliente, OCRD es la tabla de clientes, las demás son para especificar lo que queremos consultar. La consulta me debe mostrar todos los clientes hyan comprado o no.
El tema está en que no me hace bien la suma en el SUM t0.DocTotal, el resultado es mayor al que tendría que ser.
Alguna idea?
Gracias
SELECT DISTINCT T3.SlpName AS [Representante], T5.Name AS [Provincia], T1.CardCode, T1.CardName, SUM(ISNULL(T0.DocTotal,0)) AS [TOTAL]
FROM ODLN T0
RIGHT JOIN OCRD T1 ON T0.CardCode=T1.CardCode AND T0.DocDate BETWEEN '[%1]' AND '[%2]'
LEFT JOIN ( SELECT CardCode, State, City FROM CRD1 GROUP BY City,State, CardCode ) T2 ON T1.CardCode=T2.CardCode
LEFT JOIN ( SELECT SlpCode, SlpName FROM OSLP GROUP BY SlpCode,SlpName ) T3 ON T1.SlpCode=T3.SlpCode
LEFT JOIN ( SELECT GroupCode, GroupName FROM OCRG GROUP BY GroupCode, GroupName ) T4 ON T1.GroupCode=T4.GroupCode
INNER JOIN ( SELECT Code, Name FROM OCST GROUP BY Code,Name ) T5 ON T2.State=T5.Code
WHERE
(T5.name='MADRID' OR T5.name='AVILA' OR T5.name='GUADALAJARA' OR T5.name='SEGOVIA' OR T5.name='TOLEDO') AND (t4.GroupName='MOTO 44' or t4.GroupName='MOTO 50' or t4.GroupName='MOTO 37' or t4.GroupName='MOTO 30' or t4.GroupName='MOTO 40')
AND (T1.CardCode BETWEEN 'C000000' AND 'C999999')
GROUP BY T3.SlpName, T1.CardCode, T1.CardName, T4.GroupName, T5.Name, T2.State
ORDER BY T5.Name
------------------------------------------------------
La tabla ODLN es la de albaranes, donde hay varios campos, entre ellos el código y el nombre del cliente, OCRD es la tabla de clientes, las demás son para especificar lo que queremos consultar. La consulta me debe mostrar todos los clientes hyan comprado o no.
El tema está en que no me hace bien la suma en el SUM t0.DocTotal, el resultado es mayor al que tendría que ser.
Alguna idea?
Gracias
Valora esta pregunta


0