累积总和按年份变更(SQL Server)重置(SQL Server)
我正在使用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 |
---|---|---|
A | 202111 | 1 |
A | 202112 | 2 |
A | 202201 | 10 |
A | 202202 | 110 |
A | 202203 | 1110 |
B | 202111 | 2 |
B | 202112 | 4 B 202112 4 |
B | 202201 | 20 |
B | 202202 | 220 |
B | 202202 2203 22203 | 2220 |
C | 202111 | 3 |
C | 202112 6 C 202112 | 6 |
C | 202201 | 30 |
C | 202202 | 330 C 202202 330 |
C | 202203 | 3330 |
:
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);
Desired result
FACTORY | DATE_YM | C_BUDGET_SUM |
---|---|---|
A | 202111 | 1 |
A | 202112 | 2 |
A | 202201 | 10 |
A | 202202 | 110 |
A | 202203 | 1110 |
B | 202111 | 2 |
B | 202112 | 4 |
B | 202201 | 20 |
B | 202202 | 220 |
B | 202203 | 2220 |
C | 202111 | 3 |
C | 202112 | 6 |
C | 202201 | 30 |
C | 202202 | 330 |
C | 202203 | 3330 |
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 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
CTE不是必需的,但我认为这是一个简化的版本。
要扩展我的评论
结果
The CTE is not necessary, but I'm assuming this is a simplified version.
To expand on my comment
Results