Running totals for general ledger

  • 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.

  • 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

  • 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.

  • 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.

  • 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

  • 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)

  • 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

  • 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

  • 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

  • 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

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

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