Skip to Content

Sales & GP side by side comparison - with Credit Note

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Oct 15, 2015 at 10:00 AM

    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
    10|10000 characters needed characters exceeded