Skip to Content
0
Apr 10, 2014 at 12:36 PM

Sales Analysis customization query

87 Views

Hi Experts

I'm currently using this query to give me customized sales analysis. Essentially we just want to include the Sales Employee name on the report.

In most cases it matches the SAP B1 Sales Analysis report but there are also some differences. Can anyone point me in the direction of what I should be looking for in order make it match the Sales Analysis more closely?

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
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 ) 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