Running Balance per Account Number

I require to have a running balance per Account Number.

I tried developing a function, but keep getting the wrong values.

See below Sample Data and how I currently calculate the Running Balance:

declare @tbl table (id int primary key identity, Sort varchar(100), MasterAccount varchar(50), SubAccount varchar(50), Amount float)
insert into @tbl (Sort,MasterAccount,SubAccount,Amount)
values
('1000_1','1000','aaOpening Balance',78137.58)
,('1000_2_13699','1000','1000',35516.34)
,('1000_2_14379','1000','1000',16675)
,('1000_2_15141','1000','1000',9252.21)
,('1000_2_15151','1000','1000',8167.3)
,('1000_2_15161','1000','1000',5729.3)
,('1000_2_15166','1000','1000',8898.7)
,('1000_2_15623','1000','1000',3335)
,('1000_2_15633','1000','1000',2620.85)
,('1000_2_15638','1000','1000',3425.39)
,('1000_2_17582','1000','1000',7281.55)
,('1000_2_18756','1000','1000',2126)
,('1000_2_19698','1000','1000',8000)
,('1000_2_19713','1000','1000',8000)
,('1000_2_19718','1000','1000',8000)
,('1000_2_19847','1000','1000',8000)
,('1000_2_20055','1000','1000',3933.1)
,('1000_2_20060','1000','1000',5304.37)
,('1000_2_20099','1000','1000',0.00000000123)
,('1000_2_20104','1000','1000',-0.00000000123)
,('1000_2_20330','1000','1000',8000)
,('1000_2_20340','1000','1000',8000)
,('1000_2_20360','1000','1000',8000)
,('1000_2_20390','1000','1000',8000)
,('1000_2_20416','1000','1000',8000)
,('1000_2_20576','1000','1000',8000)
,('1000_2_21033','1000','1000',8000)
,('1000_3','1000','zzClosing Balance',278402.69)
,('2000_1','2000','aaOpening Balance',128023.65)
,('2000_2_14381','2000','2000',15174.5)
,('2000_2_15143','2000','2000',10534.92)
,('2000_2_15153','2000','2000',9299.6)
,('2000_2_15163','2000','2000',6523.6)
,('2000_2_15168','2000','2000',10132.4)
,('2000_2_15625','2000','2000',3084.28)
,('2000_2_15635','2000','2000',2652.48)
,('2000_2_15640','2000','2000',3466.73)
,('2000_2_17584','2000','2000',8489.38)
,('2000_2_18758','2000','2000',2132.41)
,('2000_2_20057','2000','2000',3944.96)
,('2000_2_20062','2000','2000',5320.37)
,('2000_3','2000','zzClosing Balance',208779.28)


select
*
,   round(case SubAccount when 'zzClosing Balance' then 0 else sum(Amount) over(order by [Sort] rows unbounded preceding) end,2) RunningBalance
from    @tbl

The results look as following:

The problem I am facing is the balance for the second Account Number (in this case Acc Number "2000") is calculated wrong.

I require the Running Balance to Start with the value 128,023.65 and end with 208,779.28.

To give you a better idea, my expected results would look like this:

How would I get the Expected results?

Your assistance is greatly appreciated!

Solution

I would do conditional aggregation with window function :

sum(case when SubAccount when 'zzClosing Balance' then 0 else amount end) over (partition by MasterAccount order by Sort rows unbounded preceding)