CODEWITHSUNDEEP
sqlsql-serversql-server-2012
MattC
MattC

Reputation: 135

Calculating Monthly Sales Increase/Decrease

We have a table that stores the monthly budget for our different customer segments:

SalesMonth | SalesYear | MonthBudget | CustomerType
    1      |   2018    | 275000      | Elite
    2      |   2018    | 300000      | Elite
    1      |   2018    | 150000      | Top
    2      |   2018    | 175000      | Top
    1      |   2018    | 50000       | Base
    2      |   2018    | 1000000     | Base
 etc..

I am wanting to take the previous 3 months goal (total, not by customer type) & look at the month 3 months ahead and calculate the increase/decrease % of goal. I'm able to get this to calculate for right now (to calculate August), but how would I do this for all months of the year? (realize my code is static and won't work for Jan/Feb/Mar of 2018, but 2017 data is there. We don't have 2019 data yet so I understand Oct/Nov/Dec is off as well.

with _sum as(
SELECT 
SalesMonth
,SUM([MonthBudget]) as MonthBudget
FROM [SALES].[dbo].[SALES_PLAN]
WHERE CustomerType NOT IN ('Design')
and year(dtdate) = '2018'
Group by SalesMonth
)
SELECT 
SalesMonth
,MonthBudget
,(LAG(MonthBudget,1) OVER (Order by [dtDate])+LAG(MonthBudget,2) OVER (Order     by [dtDate])+LAG(MonthBudget,3) OVER (Order by [dtDate]))/3 as Previous3AVG
,LEAD(MonthBudget,3) OVER (Order by [dtDate]) as Future3MOBudget
,(LEAD(MonthBudget,3) OVER (Order by [dtDate]))/nullif(((LAG(MonthBudget,1) OVER (Order by [dtDate])+LAG(MonthBudget,2) OVER (Order by [dtDate])+LAG(MonthBudget,3) OVER (Order by [dtDate]))/3),0) as [Change%]
   FROM [SALES].[dbo].[SALES_PLAN]

Upvotes: 2

Views: 314

Answers (1)

ccarpenter32
ccarpenter32

Reputation: 1077

One thing I was curious about was why you were using this line sum([MonthBudget])/3 as Budget when you are taking 5 months worth of data. But anyway...

It’s somewhat difficult to tell with your explanation of the data you're looking at, but this may be somewhat along the lines of what you're looking for:

WITH _past
AS (
    SELECT SUM([MonthBudget]) / 3 AS Budget
    FROM [SALES].[dbo].[SALES_PLAN]
    WHERE CustomerType NOT IN ('Design')
        AND (
            SalesMonth > ABS(Month(GetDate()) - 3)
            AND SalesYear = CASE WHEN Month(GetDate()) - 3 < 0
                    THEN Year(GetDate()) - 1
                    ELSE Year(GetDate()) END
            )
        AND SalesMonth < Month(GetDate())
    ),
_future
AS (
    SELECT SalesMonth,
        sum([MonthBudget]) AS Budget
    FROM [SALES].[dbo].[SALES_PLAN]
    WHERE CustomerType NOT IN ('Design')
        AND SalesMonth = Month(GetDate()) + 3
        AND SalesYear = CASE WHEN Month(GetDate()) + 3 > 12
                THEN Year(GetDate()) + 1
                ELSE Year(GetDate()) END
    GROUP BY SalesMonth
    ) SalesMonth

SELECT SalesMonth,
    _past.Budget / _future.budget AS [Change%]
FROM _past
CROSS APPLY _future

Obviously, it would need adjustment for whatever months are missing, etc. But you probably get the basic idea.

Upvotes: 0

Related Questions