Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

Former Member
Former Member replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question