Cumulative sum reset by change of year (SQL Server)

Question

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

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?

Answer 1

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