cancel
Showing results for 
Search instead for 
Did you mean: 

Monthly Sales By Geography

Former Member
0 Kudos

Hello All -

We would like to have a query that shows us monthly sales by geography (US State).

However, using the Bill To Address or the Ship To Address is a problem for us -- I won't get into the details but it doesn't serve our purposes very well.

Instead, we would like the location to be referenced from our account number. The first two characters of a BP's account number represents their state.

So, first step would be to consolidate sales for all BP's that share the same first two letters in their account number. This should, in theory, give us total sales for a particular state. Then, we would want to see a listing of that state's sales by month. So, result would be something like this:

Jan Feb Mar etc.

AK

AL

CA

Can anyone help with this?

Thanks!

Mike

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Mike,

Try this:


SELECT P.[SalesSTATE],
 [1] as [Jan],
 [2] as [Feb],
 [3] as [Mar],
 [4] as [Apr],
 [5] as [May],
 [6] as [Jun],
 [7] as [Jul],
 [8] as [Aug],
 [9] as [Sep],
 [10] as [Oct],
 [11] as [Nov],
 [12] as [Dec]
FROM (SELECT Left(T0.CardCode,2) as [SalesState] , T0.DocTotal, MONTH(T0.docdate) as [month] 
FROM dbo.oinv T0
WHERE Year(T0.docdate)=2008) S
  PIVOT  (SUM(DocTotal) FOR [month] IN 
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
ORDER BY P.[SalesState]

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon -

Looks very good!

Can this include Credit Memos / Returns also?

Assume this includes freight?

Also, I noticed the year is 2008...do we just change to 2010 for current year?

Thanks,

Mike

Former Member
0 Kudos

I just randomly selected a year. You may change it to any year you want.

Freight is included. However, for adding Credit Memos I have to try more.

Try this:


SELECT P.[SalesSTATE],
 [1] as [Jan],
 [2] as [Feb],
 [3] as [Mar],
 [4] as [Apr],
 [5] as [May],
 [6] as [Jun],
 [7] as [Jul],
 [8] as [Aug],
 [9] as [Sep],
 [10] as [Oct],
 [11] as [Nov],
 [12] as [Dec]
FROM (SELECT Left(T0.CardCode,2) as [SalesState] , 
Case WHEN ISNULL(T1.DocTotal,0) !=0 
THEN T1.DocTotal ELSE -T2.DocTotal END as [DocTotal], 
CASE WHEN ISNULL(T1.DocTotal,0) !=0 THEN MONTH(T1.docdate) 
ELSE MONTH(T2.docdate) END as [month] 
FROM dbo.ocrd T0
LEFT JOIN dbo.oinv T1 ON T1.CardCode = T0.CardCode AND Year(T1.docdate)=2010
LEFT JOIN dbo.ORIN T2 ON  T2.CardCode = T0.CardCode AND Year(T2.docdate)=2010
WHERE (T0.CardCode in (SELECT CardCode FROM OINV)) OR 
(T0.CardCode in (SELECT CardCode FROM ORIN)))  S
  PIVOT  (SUM(DocTotal) FOR [month] IN 
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
ORDER BY P.[SalesState]

Former Member
0 Kudos

Hi Gordon -

That doesn't work - the numbers are too high.

Any other suggestions?

Mike

Former Member
0 Kudos

Updated:


SELECT P.[STATE],
 [1] as [Jan],
 [2] as [Feb],
 [3] as [Mar],
 [4] as [Apr],
 [5] as [May],
 [6] as [Jun],
 [7] as [Jul],
 [8] as [Aug],
 [9] as [Sep],
 [10] as [Oct],
 [11] as [Nov],
 [12] as [Dec]
FROM (SELECT Left(T0.CardCode,2) as [State], 
T0.DocTotal as [DocTotal], 
MONTH(T0.docdate) as [month] 
FROM dbo.oinv T0
WHERE Year(T0.docdate)=2010
UNION
SELECT Left(T0.CardCode,2) as [State], 
-T0.DocTotal as [DocTotal], 
MONTH(T0.docdate) as [month] 
FROM dbo.orin T0
WHERE Year(T0.docdate)=2010 )  S
  PIVOT  (SUM(DocTotal) FOR [month] IN 
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
ORDER BY P.[State]

Please change very helpful mark for the above posting since it is not working.

Former Member
0 Kudos

Thanks! I think it's working! Will let you know if any issues.

Mike

Answers (0)