Running totals for general ledger

Question

Running totals for general ledger

haddoque2000

Old Hand

Points: 318
More actions
May 11, 2014 at 11:12 am

#396734

I have spent most of the weekend trying out different solutions for creating running totals. Set based and cursors. None of them worked. T-SQL has always got some gotcha in the end.
I have a table with monthly sums for assets and liabilities. I need a running total for each of the accounts. What i'd like to do should be quite simple;
- Take the first account, add the sum if its first month to the running total and update the row.
- Take the sum of next month for the same account and add it to the running total and update that row.
- And so on until another account turns up. Then we clear the running total and start over. This continues until the last period of the last account has been read and updated with a running total.
This means that which ever method I use I will have have to do some kind of sorting. Otherwise it won't work. This is where the suggested methods I have found so far usually fails. Cursors for instance - I cant update if I sort.
Is there anyone who have done this on a SQL server 2012? And, please, don't bother to answer if the solution has not been verified on SQL server 2012.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

haddoque2000 (5/11/2014)
I have spent most of the weekend trying out different solutions for creating running totals. Set based and cursors. None of them worked. T-SQL has always got some gotcha in the end.
I have a table with monthly sums for assets and liabilities. I need a running total for each of the accounts. What i'd like to do should be quite simple;
- Take the first account, add the sum if its first month to the running total and update the row.
- Take the sum of next month for the same account and add it to the running total and update that row.
- And so on until another account turns up. Then we clear the running total and start over. This continues until the last period of the last account has been read and updated with a running total.
This means that which ever method I use I will have have to do some kind of sorting. Otherwise it won't work. This is where the suggested methods I have found so far usually fails. Cursors for instance - I cant update if I sort.
Is there anyone who have done this on a SQL server 2012? And, please, don't bother to answer if the solution has not been verified on SQL server 2012.

Hi...you have posted in SQL 2008 forum....is this definitely a 2012 issue?

if it is 2012 then you do have a SQL solution....but it would be easier if you could provide some sample table create / data / expected results scripts.......that way we can all see your problem and give you tested answers based on your specific requirements.

search for running totals / windowing function in SQL 2012/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

pietlinden SSC Guru Points: 63503 More actions · Answer 2

If you have 2012, then here's an example of a running total using window functions (from Itzik Ben-Gan's book on Window functions in 2012):

SELECT empID, qty,

SUM(qty) OVER (PARTITION BY empID

ORDER BY orderMonth

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW) As RunQty

FROM Sales.EmpOrders;

PARTITION basically defines the grouping,

and ROWS BETWEEN .... defines the window. (the records you're working with, so all the records up to "this" point).

Hope that helps some. If you need the 2008 version, I have that somewhere in another one of Itzik's books.

haddoque2000 Old Hand Points: 318 More actions · Answer 3

Thanks,

I think I was a bit unclear in my post. I have managed to get that far. It's the update part that is still unsolved. That is, I want to update the same rows with the new running totals included.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Can you set up some sample data for folks to code against? Coding up a running totals script is easy. Trying to imagine how your data looks before and after the update is incredibly hard. There's a link in my sig to show you how to do it. Thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 5

haddoque2000 (5/12/2014)
Thanks,
I think I was a bit unclear in my post. I have managed to get that far. It's the update part that is still unsolved. That is, I want to update the same rows with the new running totals included.

For your own sake and the sake of this problem, please read and heed the article at the first link under "Helpful Links" in my signature line below. Also, how may rows are you talking about?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

haddoque2000 Old Hand Points: 318 More actions · Answer 6

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

haddoque2000 (5/13/2014)
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

we are missing the dbo.DimAccounts structure and data.......do I assume that dbo.DimAccounts is the table that needs to be updated with a running total?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

haddoque2000 (5/13/2014)
...
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
...

Had you read the article suggested by Jeff and others you would have posted this as INSERTs. We're now two days into your problem and haven't made any progress whatsoever. Remember what I said earlier - "Coding up a running totals script is easy...". Read the article and post the sample data in the way it recommends.

Your first post states "I have a table with monthly sums for assets and liabilities. I need a running total for each of the accounts." For this table only, post the ddl and some sample data as the source, and similar scripts for the expected result set.

Also, please clarify whether you need to update a table (as part of a data load or whatever) or return the running total as part of result set.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

haddoque2000 Old Hand Points: 318 More actions · Answer 9

Hi,

The DimAccounts table is only there to provide with the column AccountCodeAlternateKey for the WHERE filter.

It is the Amount column of [dbo].[FactFinance] I would like to update. That column is also the source column for the running totals. If this will be a problem I don't mind creating a new column for the update. I can do this alteration of the [dbo].[FactFinance] table in advance.

Best regards