Skip to Content

Sales Comparison Year on Year

Hi all,

Wondering if you can help, i am trying to get a comparison on sales figures.

It needs to include the last 3 months sales, this years sales and last years sales.

I created the code below but got stuck as it doesn't pull through the correct data, any suggestions?

SELECT T0.[CardCode], T2.[Name],

(SELECT sum(T1.[DocTotal]-T1.[VatSum]) FROM OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN [dbo].[@COMMISSION] T2 ON T0.[U_Commission] = T2.[Code] WHERE YEAR( T1.[DocDate]) = datepart(mm,getdate())-3) as 'Last 3 Months',

(SELECT sum(T1.[DocTotal]-T1.[VatSum]) FROM OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN [dbo].[@COMMISSION] T2 ON T0.[U_Commission] = T2.[Code] WHERE YEAR( T1.[DocDate]) = datepart(yy,getdate())-1) as 'Last Year',

(SELECT sum(T1.[DocTotal]-T1.[VatSum]) FROM OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN [dbo].[@COMMISSION] T2 ON T0.[U_Commission] = T2.[Code] WHERE YEAR( T1.[DocDate]) = datepart(yy,getdate())) as 'Current Year'

FROM OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] LEFT OUTER JOIN [dbo].[@COMMISSION] T2 ON T0.[U_Commission] = T2.[Code]

GROUP BY T0.[CardCode], T2.[Name]

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    Feb 13, 2017 at 03:45 PM

    Managed to resolve the issue.

    Thank you all for your help, really appreciated.

    SELECT TT.CardCode, TT.CardName, max(TT.Actual) as 'Actual Year', max(TT.Last) as 'Last Year', max(TT.months) as 'Last 3 months', TT.Name
    FROM (
    SELECT T0.CardCode, T1.CardName, T2.Name,
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate()) group by CardCode),0) as 'Actual',
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate())-1 group by CardCode),0) as 'Last',
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and datediff (day, getdate(), DocDate)>-90 group by CardCode),0) as 'months'
    FROM OINV T0
    INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode
    LEFT OUTER JOIN [@COMMISSION] T2 ON T1.[U_Commission] = T2.[Code]
    ) TT
    GROUP BY TT.CardCode, TT.CardName, TT.Name
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 09, 2017 at 04:54 PM

    Try with this...Modify to add your fields

    SELECT TT.CardCode, max(TT.Actual) as 'Actual Year', max(TT.Last) as 'Last Year', max(TT.months) as 'Last 3 months'
    FROM (
    SELECT T0.CardCode,
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate()) group by CardCode),0) as 'Actual',
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate())-1 group by CardCode),0) as 'Last',
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and datediff (day, getdate(), DocDate)>-90 group by CardCode),0) as 'months'
    FROM OINV T0
    ) TT
    GROUP BY TT.CardCode
    
    Add comment
    10|10000 characters needed characters exceeded

    • Thank you, i have tried this and it works but i am unable to add my fields, so i have clearly done them wrong any idea why?

      SELECT TT.CardCode, max(TT.Actual) as 'Actual Year', max(TT.Last) as 'Last Year', max(TT.months) as 'Last 3 months', T2.[Name]
      FROM (
      SELECT T0.CardCode
      ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate()) group by CardCode),0) as 'Actual',
      ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate())-1 group by CardCode),0) as 'Last',
      ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and datediff (day, getdate(), DocDate)>-90 group by CardCode),0) as 'months'
      FROM OINV T0 LEFT OUTER JOIN OCRD T1 ON T0.CardCode=T1.CardCode LEFT OUTER JOIN [dbo].[@COMMISSION] T2 ON T1.[U_Commission] = T2.[Code]
      ) TT
      GROUP BY TT.CardCode, T2.[Name]
  • Feb 10, 2017 at 07:38 AM

    Hi,

    Share error message from above query if any.

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 10, 2017 at 08:32 AM

    Hello, in this one i´ve added the join with OCRD and the CardName, make the same with other tables that you want

    SELECT TT.CardCode, TT.CardName, max(TT.Actual) as 'Actual Year', max(TT.Last) as 'Last Year', max(TT.months) as 'Last 3 months'
    FROM (
    SELECT T0.CardCode, T1.CardName,
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate()) group by CardCode),0) as 'Actual',
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate())-1 group by CardCode),0) as 'Last',
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and datediff (day, getdate(), DocDate)>-90 group by CardCode),0) as 'months'
    FROM OINV T0
    INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode
    ) TT
    GROUP BY TT.CardCode, TT.CardName
    
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 13, 2017 at 03:04 PM

    Thank you, it is the User Defined Table name not the value i am trying to pull through.

    The UDT is called @COMMISSION and i am trying to pull through the Name field not the value, not all BP's have a value set so i have used a left outer join.

    The issue i am having now is with the below it is not pulling through any value at all? Any suggestions as to why? Sorry to keep asking.

    SELECT TT.CardCode, TT.CardName, max(TT.Actual) as 'Actual Year', max(TT.Last) as 'Last Year', max(TT.months) as 'Last 3 months'
    FROM (
    SELECT T0.CardCode, T1.CardName, T2.Name,
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate()) group by CardCode),0) as 'Actual',
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate())-1 group by CardCode),0) as 'Last',
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and datediff (day, getdate(), DocDate)>-90 group by CardCode),0) as 'months'
    FROM OINV T0
    INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode
    LEFT OUTER JOIN [dbo].[@COMMISSION] T2 ON T1.[U_Commission] = T2.[Code]
    ) TT
    GROUP BY TT.CardCode, TT.CardName
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 14, 2017 at 11:39 AM

    But.... if you want fields of the user table, You have to add the fields in the first part of the select and in the group by ... take a look at this, if no... there is no necesary....try with left join simple

    SELECT TT.CardCode, TT.CardName, TT.U_Commission, max(TT.Actual) as 'Actual Year', max(TT.Last) as 'Last Year', max(TT.months) as 'Last 3 months'
    FROM (
    SELECT T0.CardCode, T1.CardName, T2.Name, T1.U_Commission, 
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate()) group by CardCode),0) as 'Actual',
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate())-1 group by CardCode),0) as 'Last',
    ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and datediff (day, getdate(), DocDate)>-90 group by CardCode),0) as 'months'
    FROM OINV T0
    INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode
    LEFT JOIN [dbo].[@COMMISSION] T2 ON T1.U_Commission = T2.Code
    ) TT
    GROUP BY TT.CardCode, TT.CardName, T1.U_Commission
    Add comment
    10|10000 characters needed characters exceeded