Sales & GP side by side comparison - with Credit Note

[Former Member]

Hi,

   I am trying to create a query showing Sales/GP with YTD2014 and YTD2015 comparison for each BP. This was resolved in an earlier question but I realised the sales numbers did not include credit note.  I tried adding ORIN/RIN1 to this query format but unsuccessful. Can anyone help to add the fix for CN?. Thanks.

declare @from as datetime = CAST('2015/01/01' AS DATETIME)

declare @to as datetime = CAST('2015/01/31' AS DATETIME)

SELECT T0.CardCode

, SUM(ISNULL(T1.LineTotal,0) ) AS SalesYear14

, SUM(T1.GrssProfit) 'Gross Profit14', SUM(T1.GrssProfit)/ SUM(T1.LineTotal)*100  'GP% 14'

, SUM(ISNULL(T2.LineTotal,0) ) AS SalesYear15

, SUM(T2.GrssProfit) 'Gross Profit15', SUM(T2.GrssProfit)/ SUM(T2.LineTotal)*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

ORDER BY T0.CardCode

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Thanks J.  Apologies for not marking as Correct earlier.