累积总和按年份变更(SQL Server)重置(SQL Server)

发布于 2025-02-03 03:26:21 字数 2032 浏览 3 评论 0原文

我正在使用SQL Server 2017。我想总结那一年和工厂的每月预算。

每个新年将重置此累积。

表格架:

CREATE TABLE [TABLE_1] 
(
    FACTORY varchar(50) Null,
    DATE_YM int Null,
    BUDGET int NULL,
);

INSERT INTO TABLE_1 (FACTORY, DATE_YM, BUDGET)
VALUES ('A', 202111, 1),
       ('A', 202112, 1),
       ('A', 202201, 10),
       ('A', 202202, 100),
       ('A', 202203, 1000),
       ('B', 202111, 2),
       ('B', 202112, 2),
       ('B', 202201, 20),
       ('B', 202202, 200),
       ('B', 202203, 2000),
       ('C', 202111, 3),
       ('C', 202112, 3),
       ('C', 202201, 30),
       ('C', 202202, 300),
       ('C', 202203, 3000);

=

6c62ab2a25a476c92853520799d61d61c90总和fiddle
A2021111
A2021122
A20220110
A202202110
A2022031110
B2021112
B2021124 B 202112 4
B20220120
B202202220
B202202 2203 222032220
C2021113
C202112 6 C 2021126
C20220130
C202202330 C 202202 330
C2022033330

WITH data AS
(
    SELECT
        T1.FACTORY,
        T1.DATE_YM,
        T1.BUDGET
    FROM
        TABLE_1 AS T1
)
SELECT
    FACTORY,
    DATE_YM,
    SUM(BUDGET) OVER (ORDER BY FACTORY, DATE_YM ASC 
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'C_BUDGET_SUM'
FROM
    data

我的方法 年结束。如何动态实施一年的休息时间?

I'm using SQL Server 2017. I would like to sum up the budget per month of a year for that year and factory.

This cumulation is to be reset with each new year.

Table schema:

CREATE TABLE [TABLE_1] 
(
    FACTORY varchar(50) Null,
    DATE_YM int Null,
    BUDGET int NULL,
);

INSERT INTO TABLE_1 (FACTORY, DATE_YM, BUDGET)
VALUES ('A', 202111, 1),
       ('A', 202112, 1),
       ('A', 202201, 10),
       ('A', 202202, 100),
       ('A', 202203, 1000),
       ('B', 202111, 2),
       ('B', 202112, 2),
       ('B', 202201, 20),
       ('B', 202202, 200),
       ('B', 202203, 2000),
       ('C', 202111, 3),
       ('C', 202112, 3),
       ('C', 202201, 30),
       ('C', 202202, 300),
       ('C', 202203, 3000);

LINK TO db<>fiddle

Desired result

FACTORYDATE_YMC_BUDGET_SUM
A2021111
A2021122
A20220110
A202202110
A2022031110
B2021112
B2021124
B20220120
B202202220
B2022032220
C2021113
C2021126
C20220130
C202202330
C2022033330

My approach:

WITH data AS
(
    SELECT
        T1.FACTORY,
        T1.DATE_YM,
        T1.BUDGET
    FROM
        TABLE_1 AS T1
)
SELECT
    FACTORY,
    DATE_YM,
    SUM(BUDGET) OVER (ORDER BY FACTORY, DATE_YM ASC 
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'C_BUDGET_SUM'
FROM
    data

This query totals across year ends. How can the year break be implemented dynamically?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

转身泪倾城 2025-02-10 03:26:21

CTE不是必需的,但我认为这是一个简化的版本。

要扩展我的评论

with data as (
select
  T1.FACTORY,
  T1.DATE_YM,
  T1.BUDGET
from TABLE_1 as T1
)
select
FACTORY,
  DATE_YM,
  sum(BUDGET) over (partition by Factory,left(Date_YM,4) order by DATE_YM asc rows between unbounded preceding and current row) as 'C_BUDGET_SUM'
from data

结果

”在此处输入图像描述”

The CTE is not necessary, but I'm assuming this is a simplified version.

To expand on my comment

with data as (
select
  T1.FACTORY,
  T1.DATE_YM,
  T1.BUDGET
from TABLE_1 as T1
)
select
FACTORY,
  DATE_YM,
  sum(BUDGET) over (partition by Factory,left(Date_YM,4) order by DATE_YM asc rows between unbounded preceding and current row) as 'C_BUDGET_SUM'
from data

Results

enter image description here

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文