Performing cumulative calculations in SQL

summary

The cumulative value of the amount in year-month units is calculated using SQL.

Operation check version

Supported SQL Server Versions

• 2005
• 2008
• 2008 R2

Check SQL Server Version

• 2005
• 2008
• 2008 R2

Tested Windows version

• 2008
• 2008 R2

substance

Normally, when using a database, the amount of money is often calculated for the specified year and month, but there are also cases where the amount of this month is added based on the data of the previous month such as amount and inventory. You can calculate cumulative values stored or programmatically, but for now I want to do the calculation only in SQL.

There is a table below, with amounts set for each year and month:

In order to update in a batch in SQL, instead of using "the amount of the previous month" as "the amount of the previous month", you can calculate the cumulative value by attaching the condition that the past amount is aggregated from the target year and the month.

update 金額集計Test
  set 金額累計 = 累計
from
(
  select
    A.年月,
    sum(B.金額) as 累計
  from 金額集計Test A
  left outer join 金額集計Test B
    on B.年月 <= A.年月
  group by A.年月
) as C
where 金額集計Test.年月 = C.年月

When you execute the above SQL, the cumulative value is entered in "Amount Cumulative".

In addition, if you want to reset the cumulative value every year, you can calculate it by changing the conditional expression.

update 金額集計Test
  set 年度単位金額累計 = 累計
from
(
select
  A.年月,
  sum(B.金額) as 累計
from 金額集計Test A
left outer join 金額集計Test B
  on B.年月 <= A.年月
  and B.年月 >= case
                  when cast(right(A.年月, 2) as int) >= 4 then left(A.年月, 4) + N'04'
                  else cast(cast(left(A.年月, 4) as int) - 1 as nvarchar(4)) + N'04'
                end
group by A.年月
) as C
where 金額集計Test.年月 = C.年月

When you execute the above SQL, the cumulative value is entered in "Annual Amount Cumulative".