The original table looks like this:

CREATE TABLE [dbo].[FactFinance](

[FinanceKey] [int] IDENTITY(1,1) NOT NULL,

[DateKey] [int] NOT NULL,

[OrganizationKey] [int] NOT NULL,

[ClientKey] [int] NOT NULL,

[AccountKey] [int] NOT NULL,

[Dim1Key] [int] NOT NULL,

[Dim2Key] [int] NOT NULL,

[Amount] [float] NOT NULL,

[Date] [datetime] NULL

Sample rows:

FinKeyDateKeyOrgaKeyCliKeyAccoKeyDim1KeyDim2KeyAmountDate

19720101001113611-541261,822010-10-01 00:00:00.000

19820101031113611-541261,822010-10-31 00:00:00.000

19920101101113611-550252,822010-11-01 00:00:00.000

20020101201113611-559243,822010-12-01 00:00:00.000

20120110131113611-568234,822011-01-31 00:00:00.000

2022004050611117116103332004-05-06 00:00:00.000

2032004050711117111436942004-05-07 00:00:00.000

204200405101111711-1466222004-05-10 00:00:00.000

205200405111111711-1595222004-05-11 00:00:00.000

My plan is to update the Amount column of some of the accounts (AccountCodeAlternateKey < 3000 - see below) with a running total.

I started of with a temp table. The I tried different aproaches. None of them worked out.

The number of rows should be no more than 50 000.

IF OBJECT_ID('tempdb..#TransTmp',N'U') IS NOT NULL

DROP TABLE #TransTmp

GO

SELECT CONCAT(CAST(DK.AccountCodeAlternateKey AS nvarchar(4)),

CAST(RIGHT(10000 + COALESCE(FR.OrganizationKey,0),3) AS nvarchar(4)),

CAST(RIGHT(10000 + COALESCE(FR.Dim1Key,0),3) AS nvarchar(4)),

CAST(RIGHT(10000 + COALESCE(FR.Dim2Key,0),3) AS nvarchar(4))

) AS SortCombo

,FR.Date

,Amount

,CAST ( 0 AS Float) AS Balance

,FR.FinanceKey

INTO #TransTmp

FROM dbo.FactFinance AS FR

INNER JOIN dbo.DimAccounts AS DK

ON FR.AccountKey = DK.AccountKey

WHERE DK.AccountCodeAlternateKey < 3000