计算每月销售额增加/减少-Java 学习之路

我们有一个表格，用于存储不同客户群的每月预算：

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

我想要采取前3个月的目标（总计，而不是客户类型）并查看未来3个月的月份并计算目标的增加/减少百分比 . 我现在可以计算这个（计算八月），但是我怎么能在一年中的所有月份都这样做呢？（实现我的代码是静态的，不会在2018年1月/ 2月/ 3月工作，但2017年的数据是存在的 . 我们还没有2019年的数据，所以我理解10月/ 11月/ 12月也是关闭的 .

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]

1 回答

我很好奇的一件事是你在使用5个月的数据时使用这条线 sum([MonthBudget])/3 as Budget . 但无论如何...

用您对所查看数据的解释有点难以辨别，但这可能与您正在寻找的内容有所不同：

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

显然，它需要调整任何缺失的月份等等 . 但你可能得到了基本的想法 .

回复于 2024-05-04T09:05:39+08:00

计算每月销售额增加/减少

1 回答

相关问题