Click here to Skip to main content
12,451,089 members (54,085 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

19.3K views
9 bookmarked
Posted

SQL. Running Totals.

, 20 Jun 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Running totals... Accountants like them so much. But there is no way in SQL to make efficient query that calculate running total. Why? What is running total? It is just previous running total plus current value. But SQL syntax does not give an access to previous rows.

Running totals... Accountants like them so much. But there is no way in MS SQL Server to make efficient query that calculate running total. Why? What is running total? It is just previous running total plus current value. But T-SQL does not give an access to previous rows. I search the Internet for the running totals. Every link I click I saw 2 methods to calculate running totals

  • join or subquery the table. Here we have n2 operations
  • use cursor and temporary table. Here we have to insert into temporary table and then to join on the basis query.

So, both ways are not so fast as I need to. But one can feel that if to use procedural languages it may be done very fast.

Yes! .NET does help us since Microsoft added seamless support of .NET into the SQL Server!

The idea is to create a .NET function that can remember values of previous row and force desired order for the running total in SQL query.

First we need a function to keep previous running total. As far as SQL Server is multithread environment we should guaranty thread-safety and keep each running total in thread-related variable. It is good idea to use CallContext class for this purpose.

using System;
using System.Runtime.Remoting.Messaging;

namespace RunningTotal
{
    public static class RunningTotalUtils
    {
        public static decimal RunningTotal(decimal currentValue)
        {
            object _lastTotal = CallContext.GetData("runningTotal");
            decimal lastTotal = _lastTotal == null ?
              0 : Convert.ToDecimal(_lastTotal);
            lastTotal += currentValue;
            CallContext.SetData("runningTotal", lastTotal);
            return lastTotal;
        }
    }
}
Now it’s time to create a T-SQL wrapper for the function:
 
CREATE ASSEMBLY RunningTotal
FROM 'C:\RunningTotal.dll'
WITH PERMISSION_SET = UNSAFE;
GO

CREATE FUNCTION RunningTotal (@amount decimal(18,2))
RETURNS decimal(18,2)
AS EXTERNAL NAME
  RunningTotal.[RunningTotal.RunningTotalUtils].RunningTotal;
GO

And... the fastest ever running totals come with the following SQL query:

select *, dbo.RunningTotal(Amount) Total from Transactions;

This is correct for the following table

create table Transactions
(
   ID int identity primary key,
   Amount decimal(18,2)
);

It seems no one can do it faster.

In future Microsoft will probably add support for windowing and this article become obsolete. For instance, in PostgreSQL 8.4 we may do the following

select *, sum(Amount) over(order by ID) Total from Transactions;

The result will be the same.

Bye.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Anton Burtsev
Web Developer
Russian Federation Russian Federation
I have started as a C++ developer in far 2000. I'd been developing banking and treasury software for 3 years using C++. In 2003 I switched to .NET at DELL. After that I worked as a project manager on different projects (internal audit, treasury automation, publishing house automation, etc.). Since 2009 I own a small software company specialized in SaaS services and develop a DotNetNuke modules.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralUsing WITH ROLLUP Pin
spoodygoon19-Jun-09 8:23
memberspoodygoon19-Jun-09 8:23 
GeneralRe: Using WITH ROLLUP [modified] Pin
Anton Burtsev20-Jun-09 0:24
memberAnton Burtsev20-Jun-09 0:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160826.1 | Last Updated 20 Jun 2009
Article Copyright 2009 by Anton Burtsev
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid