Hi,
Use this
declare @from as datetime = CAST('2015/01/01' AS DATETIME)
declare @to as datetime = CAST('2015/01/31' AS DATETIME);
with ct as (
SELECT T0.CardCode
, SUM(ISNULL(T1.LineTotal,0) ) AS SalesYear14
, SUM(ISNULL(T1.GrssProfit,0)) 'Gross Profit14', SUM(ISNULL(T1.GrssProfit,0))/SUM(ISNULL(T1.LineTotal,1) )*100 'GP% 14'
, SUM(ISNULL(T2.LineTotal,0) ) AS SalesYear15
, SUM(ISNULL(T2.GrssProfit,0)) 'Gross Profit15', SUM(ISNULL(T2.GrssProfit,0))/SUM(ISNULL(T2.LineTotal,1) )*100 'GP% 15'
FROM OINV T0
LEFT OUTER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry AND T1.DocDate BETWEEN DATEADD(YEAR, -1, @from) AND DATEADD(YEAR, -1, @to)
LEFT OUTER JOIN INV1 T2 ON T2.DocEntry = T0.DocEntry AND T2.DocDate BETWEEN @from AND @to
WHERE T0.DocType = 'I'
GROUP BY T0.CardCode
HAVING SUM(ISNULL(T1.LineTotal,0)) > 0 OR SUM(ISNULL(T2.LineTotal,0)) > 0
union all
SELECT T0.CardCode
, -SUM(ISNULL(T1.LineTotal,0) ) AS SalesYear14
, -SUM(ISNULL(T1.GrssProfit,0)) 'Gross Profit14', -SUM(ISNULL(T1.GrssProfit,0))/SUM(ISNULL(T1.LineTotal,1) )*100 'GP% 14'
, -SUM(ISNULL(T2.LineTotal,0) ) AS SalesYear15
, -SUM(ISNULL(T2.GrssProfit,0)) 'Gross Profit15', -SUM(ISNULL(T2.GrssProfit,0))/SUM(ISNULL(T2.LineTotal,1) )*100 'GP% 15'
FROM ORIN T0
LEFT OUTER JOIN RIN1 T1 ON T1.DocEntry = T0.DocEntry AND T1.DocDate BETWEEN DATEADD(YEAR, -1, @from) AND DATEADD(YEAR, -1, @to)
LEFT OUTER JOIN RIN1 T2 ON T2.DocEntry = T0.DocEntry AND T2.DocDate BETWEEN @from AND @to
WHERE T0.DocType = 'I'
GROUP BY T0.CardCode
HAVING SUM(ISNULL(T1.LineTotal,0)) > 0 OR SUM(ISNULL(T2.LineTotal,0)) > 0
)
select ct.CardCode ,sum(ct.SalesYear14 ) SalesYear14 ,sum(ct.[Gross Profit14] ) [Gross Profit14]
,sum(ct.[GP% 14] ) [GP% 14],sum(ct.SalesYear15 ) SalesYear15,
sum(ct.[Gross Profit15] ) [Gross Profit15],sum(ct.[GP% 15] ) [GP% 15]
from ct
group by ct.CardCode
Add comment