Skip to Content
0

Sales League table query

Mar 07, 2017 at 12:29 PM

18

avatar image
Former Member

Hi Experts

I have a pivot query which I use to show monthly sales per sales employee. The formula which I am using is the invoice totals minus the credit totals in each month.

What I want to know how is how to go about adapting the query to show the data at a daily level for a particular month. Eventually, I want to set up an email which sends the query data as a daily email to sales employees adding a new column for each working day.

SELECT 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 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) = 2017
UNION ALL
SELECT 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) = 2017) S
PIVOT  (SUM(S.[BAL]) FOR [month] IN 
         ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
Order By P.SlpName

Many thanks

Geoff

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers