Skip to Content
0
Former Member
Mar 05, 2009 at 05:00 PM

Help with Query please

22 Views

I have this query that will show me sales and credits by Years, the problem is that the query displays an Row for Sales and a Row for Credits. Is there any way to merge the two rows onto one row with the proper calculation of sales less credits. (see query below)

Regards and any help is appreciated

M.Jenkins

SELECT T0.CardCode, T0.CardName,

(SELECT SUM(DocTotal - TotalExpns - VatSum) FROM OINV WHERE CardCode = T0.CardCode and numatcard not like '%INTERNA%'

AND YEAR(DocDate) = '2007') AS 'Sales 2007',

(SELECT SUM(DocTotal - TotalExpns - VatSum) FROM OINV WHERE CardCode = T0.CardCode and numatcard not like '%INTERNA%'

AND YEAR(DocDate) = '2008') AS [Sales 2008],

(SELECT SUM(DocTotal - TotalExpns - VatSum) FROM OINV WHERE CardCode = T0.CardCode and numatcard not like '%INTERNA%'

AND YEAR(DocDate) = '2009') AS [Sales 2009]

FROM [dbo].[OCRD] T0 LEFT JOIN [dbo].[OINV] T1 ON T1.CardCode = T0.CardCode

WHERE T1.DocDate BETWEEN '[%0]' AND '[%1]'

UNION

SELECT T0.CardCode, T0.CardName,

(SELECT SUM(DocTotal - TotalExpns - VatSum) * -1 FROM ORIN WHERE CardCode = T0.CardCode and numatcard not like '%INTERNA%'

AND YEAR(DocDate) = '2007') AS [Sales 2007],

(SELECT SUM(DocTotal - TotalExpns - VatSum) * -1 FROM ORIN WHERE CardCode = T0.CardCode and numatcard not like '%INTERNA%'

AND YEAR(DocDate) = '2008') AS [Sales 2008],

(SELECT SUM(DocTotal - TotalExpns - VatSum) * -1 FROM ORIN WHERE CardCode = T0.CardCode and numatcard not like '%INTERNA%'

AND YEAR(DocDate) = '2009') AS [Sales 2009]

FROM [dbo].[OCRD] T0 LEFT JOIN [dbo].[OINV] T1 ON T1.CardCode = T0.CardCode

WHERE T1.DocDate BETWEEN '[%0]' AND '[%1]'

GROUP BY T0.CardCode, T0.CardName