Sales Analysis Query

[former_member834429]

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

[Former Member · Accepted solution]

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

[former_member834429]

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

[former_member184146]

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