Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Running totals for general ledger Expand / Collapse
Author
Message
Posted Sunday, May 11, 2014 11:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 3:44 AM
Points: 4, Visits: 22
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.
Post #1569637
Posted Sunday, May 11, 2014 11:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:59 AM
Points: 1,886, Visits: 18,540
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 !
__________________________________________________________________
Post #1569642
Posted Sunday, May 11, 2014 3:20 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 706, Visits: 4,507
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.
Post #1569651
Posted Monday, May 12, 2014 6:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 3:44 AM
Points: 4, Visits: 22
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.
Post #1569794
Posted Monday, May 12, 2014 7:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 7,123, Visits: 13,496
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
Post #1569833
Posted Monday, May 12, 2014 4:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1570086
Posted Tuesday, May 13, 2014 12:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 3:44 AM
Points: 4, Visits: 22
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
Post #1570146
Posted Tuesday, May 13, 2014 2:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:59 AM
Points: 1,886, Visits: 18,540
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 !
__________________________________________________________________
Post #1570168
Posted Tuesday, May 13, 2014 2:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 7,123, Visits: 13,496
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
Post #1570173
Posted Wednesday, May 14, 2014 6:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 3:44 AM
Points: 4, Visits: 22
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

Post #1570784
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse