Skip to Content
avatar image
Former Member

Sales Analysis Query

Hi Experts

I run this query which shows me Sales Analysis (Invoice - Credit Notes) data for all BPs.   The only reason I use instead of Sales Analysis is that I want to show the default salesperson from OCRD in the report and I have some PHP running on the server which automatically runs the emails the result set each week to the sales guys.    On checking the data alongside the Sales Analysis report in SAP B1 I notice that some of the totals are not the same as the Sales Analysis report.    Can anyone spot what it wrong?  or do any experts have a better solution for producing Sales Analysis in a query?

Kind Regards

Geoff

SELECT P.[CardCode],P.[CardName],P.[SlpName],

ISNULL([1],0) as [Jan],

ISNULL([2],0) as [Feb],

ISNULL([3],0) as [Mar],

ISNULL([4],0) as [Apr],

ISNULL([5],0) as [May],

ISNULL([6],0) as [Jun],

ISNULL([7],0) as [Jul],

ISNULL([8],0) as [Aug],

ISNULL([9],0) as [Sep],

ISNULL([10],0) as [Oct],

ISNULL([11],0) as [Nov],

ISNULL([12],0) as [Dec]

FROM (SELECT T0.CARDCODE, T0.CARDNAME, T2.SlpName,(T1.Doctotal-T1.VatSum-T1.TotalExpns) AS [BAL],MONTH(T1.Docdate) as [month] FROM dbo.OCRD T0

LEFT JOIN dbo.oslp t2 on t0.slpcode=t2.slpcode         

LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode AND Year(T1.Docdate) = 2014 where T0.[CardType] = 'c'

          UNION

          SELECT T0.CARDCODE, T0.CARDNAME,T2.SlpName, - (T1.Doctotal-T1.VatSum-T1.TotalExpns)  AS [BAL],MONTH(T1.Docdate) as [month] FROM dbo.OCRD T0  

LEFT JOIN dbo.oslp t2 on t0.slpcode=t2.slpcode  

LEFT JOIN dbo.ORIN T1 ON T1.CardCode = T0.CardCode AND Year(T1.Docdate) = 2014 where T0.[CardType] = 'c' ) S

          PIVOT  (SUM(S.[BAL]) FOR [month] IN

         ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Order By P.CardCode

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Jan 06, 2015 at 06:53 PM

    Hi,

    Try this instead:

    SELECT T0.CARDCODE, T0.CARDNAME, T2.SlpName,(T1.Doctotal-T1.VatSum-T1.TotalExpns) AS [BAL],MONTH(T1.Docdate) as [month] FROM dbo.OINV T1

    JOIN dbo.OCRD T0 ON T1.CardCode = T0.CardCode

    LEFT JOIN dbo.oslp t2 on t0.slpcode=t2.slpcode

    where Year(T1.Docdate) = 2014

    UNION ALL

    SELECT T0.CARDCODE, T0.CARDNAME, T2.SlpName,-(T1.Doctotal-T1.VatSum-T1.TotalExpns) AS [BAL],MONTH(T1.Docdate) as [month] FROM dbo.ORIN T1

    JOIN dbo.OCRD T0 ON T1.CardCode = T0.CardCode

    LEFT JOIN dbo.oslp t2 on t0.slpcode=t2.slpcode

    where Year(T1.Docdate) = 2014

    If you still not got the same total, you probably have empty default sales employees for some customers.

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 06, 2015 at 06:47 PM

    Hi,

         Try the Below Query

    SELECT P.[CardCode],P.[CardName],P.[SlpName],

    ISNULL([1],0) as [Jan],

    ISNULL([2],0) as [Feb],

    ISNULL([3],0) as [Mar],

    ISNULL([4],0) as [Apr],

    ISNULL([5],0) as [May],

    ISNULL([6],0) as [Jun],

    ISNULL([7],0) as [Jul],

    ISNULL([8],0) as [Aug],

    ISNULL([9],0) as [Sep],

    ISNULL([10],0) as [Oct],

    ISNULL([11],0) as [Nov],

    ISNULL([12],0) as [Dec]

    FROM (SELECT M.CARDCODE, M.CARDNAME, e.SlpName,(a.Doctotal-a.VatSum-a.TotalExpns) AS [BAL],MONTH(a.Docdate) as [month]

    FROM oinv a INNER JOIN

                          INV1 b ON a.DocEntry = b.DocEntry INNER JOIN

                          OITM c ON b.itemcode = c.ItemCode INNER JOIN

                          OITB d ON c.ItmsGrpCod = d .ItmsGrpCod INNER JOIN

                          OSLP e ON a.SlpCode = e.SlpCode INNER JOIN

                          OWHS l ON b.WhsCode = l.WhsCode INNER JOIN

       INV12 V ON A.DocEntry=V.DocEntry INNER JOIN

       OCRD M ON M.CardCode=A.CardCode AND Year(a.Docdate) = 2014 where M.[CardType] = 'c'

              UNION

              SELECT N.CARDCODE, N.CARDNAME,j.SlpName, - (f.Doctotal-f.VatSum-f.TotalExpns)  AS [BAL],MONTH(f.Docdate) as [month]

      

        FROM ORIN f INNER JOIN

                          RIN1 g ON f.DocEntry = g.DocEntry INNER JOIN

                          OITM h ON g.ItemCode = h.ItemCode INNER JOIN

                          OITB i ON h.ItmsGrpCod = i.itmsGrpCod INNER JOIN

                          OSLP j ON f.SlpCode = j.slpcode INNER JOIN

                          OWHS k ON g.WhsCode = k.WhsCode INNER JOIN

       INV12 P ON F.DocEntry=P.DocEntry inner join

       OCRD N ON N.CardCode=F.CardCode AND Year(f.Docdate) = 2014 where N.[CardType] = 'c' ) S

              PIVOT  (SUM(S.[BAL]) FOR [month] IN

             ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

    Order By P.CardCode

    and let me know the result.

    Regards,

    Manish

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 07, 2015 at 11:56 AM

    Hi,

    Manish - thanks for your help but it produced the same results as my query.

    Gordon - thanks for your query.   This does give me the correct results - it shows a row for each invoice which when pivoted in excel balances with the totals in Sales Analysis.

    Once I had created the pivot version in SAP B1 I could see the correct results but I checked the invoices there weren't any with empty sales employees so I can't figure out what I did wrong.   Still it is working now!!  😊

    SELECT P.[CardCode],P.[CardName],P.[SlpName],

    ISNULL([1],0) as [Jan],

    ISNULL([2],0) as [Feb],

    ISNULL([3],0) as [Mar],

    ISNULL([4],0) as [Apr],

    ISNULL([5],0) as [May],

    ISNULL([6],0) as [Jun],

    ISNULL([7],0) as [Jul],

    ISNULL([8],0) as [Aug],

    ISNULL([9],0) as [Sep],

    ISNULL([10],0) as [Oct],

    ISNULL([11],0) as [Nov],

    ISNULL([12],0) as [Dec]

    FROM (SELECT T0.CARDCODE, T0.CARDNAME, T2.SlpName,(T1.Doctotal-T1.VatSum-T1.TotalExpns) AS [BAL],MONTH(T1.Docdate) as [month] FROM dbo.OINV T1

    JOIN dbo.OCRD T0 ON T1.CardCode = T0.CardCode

    LEFT JOIN dbo.oslp t2 on t0.slpcode=t2.slpcode

    where Year(T1.Docdate) = 2014

    UNION ALL

    SELECT T0.CARDCODE, T0.CARDNAME, T2.SlpName,-(T1.Doctotal-T1.VatSum-T1.TotalExpns) AS [BAL],MONTH(T1.Docdate) as [month] FROM dbo.ORIN T1

    JOIN dbo.OCRD T0 ON T1.CardCode = T0.CardCode

    LEFT JOIN dbo.oslp t2 on t0.slpcode=t2.slpcode

    where Year(T1.Docdate) = 2014) S

    Add comment
    10|10000 characters needed characters exceeded