Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running totals for general ledger


Running totals for general ledger

Author
Message
haddoque2000
haddoque2000
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 27
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.
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3456 Visits: 33049
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
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2195 Visits: 12563
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
haddoque2000
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 27
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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9027 Visits: 19036
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
Exploring Recursive CTEs by Example Dwain Camps
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45390 Visits: 39941
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
haddoque2000
haddoque2000
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 27
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:
FinKey DateKey OrgaKey CliKey AccoKey Dim1Key Dim2Key Amount Date
197 20101001 1 1 36 1 1 -541261,82 2010-10-01 00:00:00.000
198 20101031 1 1 36 1 1 -541261,82 2010-10-31 00:00:00.000
199 20101101 1 1 36 1 1 -550252,82 2010-11-01 00:00:00.000
200 20101201 1 1 36 1 1 -559243,82 2010-12-01 00:00:00.000
201 20110131 1 1 36 1 1 -568234,82 2011-01-31 00:00:00.000
202 20040506 1 1 117 1 1 610333 2004-05-06 00:00:00.000
203 20040507 1 1 117 1 1 143694 2004-05-07 00:00:00.000
204 20040510 1 1 117 1 1 -146622 2004-05-10 00:00:00.000
205 20040511 1 1 117 1 1 -159522 2004-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
J Livingston SQL
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3456 Visits: 33049
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:
FinKey DateKey OrgaKey CliKey AccoKey Dim1Key Dim2Key Amount Date
197 20101001 1 1 36 1 1 -541261,82 2010-10-01 00:00:00.000
198 20101031 1 1 36 1 1 -541261,82 2010-10-31 00:00:00.000
199 20101101 1 1 36 1 1 -550252,82 2010-11-01 00:00:00.000
200 20101201 1 1 36 1 1 -559243,82 2010-12-01 00:00:00.000
201 20110131 1 1 36 1 1 -568234,82 2011-01-31 00:00:00.000
202 20040506 1 1 117 1 1 610333 2004-05-06 00:00:00.000
203 20040507 1 1 117 1 1 143694 2004-05-07 00:00:00.000
204 20040510 1 1 117 1 1 -146622 2004-05-10 00:00:00.000
205 20040511 1 1 117 1 1 -159522 2004-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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9027 Visits: 19036
haddoque2000 (5/13/2014)
...
Sample rows:
FinKey DateKey OrgaKey CliKey AccoKey Dim1Key Dim2Key Amount Date
197 20101001 1 1 36 1 1 -541261,82 2010-10-01 00:00:00.000
198 20101031 1 1 36 1 1 -541261,82 2010-10-31 00:00:00.000
199 20101101 1 1 36 1 1 -550252,82 2010-11-01 00:00:00.000
200 20101201 1 1 36 1 1 -559243,82 2010-12-01 00:00:00.000
201 20110131 1 1 36 1 1 -568234,82 2011-01-31 00:00:00.000
202 20040506 1 1 117 1 1 610333 2004-05-06 00:00:00.000
203 20040507 1 1 117 1 1 143694 2004-05-07 00:00:00.000
204 20040510 1 1 117 1 1 -146622 2004-05-10 00:00:00.000
205 20040511 1 1 117 1 1 -159522 2004-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
Exploring Recursive CTEs by Example Dwain Camps
haddoque2000
haddoque2000
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 27
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search