none
Running total in SELECT RRS feed

  • Question

  • I have a SQL 2008 database table that holds time worked in minutes (MinutesWorked column) and I want to have the SELECT show that running total for each EmployeeCode value.  Below is the table structure and a sample SELECT. Any help is appreciated.

    CREATE TABLE [dbo].[RepairOrderTasks](
     [TaskID] [int] IDENTITY(1,1) NOT NULL,
     [EmployeeCode] [varchar](8) NULL,
     [DateEntered] [smalldatetime] NULL,
     [StartTime] [smalldatetime] NULL,
     [EndTime] [smalldatetime] NULL,
     [MinutesWorked] [int] NULL
     CONSTRAINT [PK_RepairOrderTasks] PRIMARY KEY CLUSTERED
    (
     [TaskID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]


    SELECT [EmployeeCode]
          ,[DateEntered]
          ,[StartTime]
          ,[EndTime]
          ,[MinutesWorked]
      FROM dbo.RepairOrderTasks

    Wednesday, November 17, 2010 1:52 PM

Answers

All replies

  • If your table is huge I think better is (in terms of performance) to use cursors. As alternative if you have book name SQL Server MVP Deep Dives          
    Hugo has written great script for the subject (IIRW)

    Set-based iteration: the third alternative by Hugo Kornelis


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 17, 2010 2:02 PM
    Answerer
  • Take a look at this thread.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/14a92db0-7c0a-4be6-b8c4-9b2dce8863c8/

     

    There are many posts on the forum that deals with running totals. Just type Running Totals in the search box and you can see the results.


    Abdallah El-Chal, PMP, ITIL, MCTS
    Wednesday, November 17, 2010 2:07 PM
  • There are several approaches like using a cursor, a correlated subquery, a self join, a SQLCLR function, etc. The set based ones do not perform well if both the partition (rows by employee) or the table is big enough.

    There is a suggestion in connect.microsoft.com, asking Microsoft for the full support of the OVER clause, that will help us to do this kind of calculation in a simple way and is supposed to perform well too. Feel free to add your vote if you think that we should have this functionality in future versions of SQL Server.

    OVER clause enhancement request - Progressive ordered calculations

    Here are some interesting links.

    Subqueries and Joins for Running Aggregates

    Set-Based vs. Cursor-Based Solutions for Running Aggregates

    Ordered UPDATE and Set-Based Solutions to Running Aggregates

    Running sums, redux

    Running sums yet again: SQLCLR saves the day!

     

    Example: (could be not the one you need, since the schema is not self descriptive enough)

    select
        A.*,
        (
        select
            sum(B.minutesworked)
        from
            dbo.RepairOrderTasks as B
        where
            B.employeecode = A.employeecode
            and (B.dateentered < A.dateentered or (B.dateentered = A.dateentered and B.starttime <= A.starttime))
        ) as rt
    from dbo.RepairOrderTasks as A;

    I didn't take in mind that tasks could be running in parallel.

     


    AMB

    Some guidelines for posting questions...

    Wednesday, November 17, 2010 4:16 PM
    Moderator
  • There is a RUNNINGVALUE function in Reporting Services that takes three parameters, and very easy to use. I wonder what does take Microsoft to have a similar function in t-sql.
    Abdallah El-Chal, PMP, ITIL, MCTS
    Wednesday, November 17, 2010 4:34 PM
  • The windowing functions in SQL 2012 give you the tools you need to accomplish this. Below is a simple example that uses the AdventureWorksDW2012 database to provide YTD running totals.

    select
        f.OrderDate
        ,f.ProductKey
        ,year(f.orderdate) as OrderYear
        ,sum(f.SalesAmount) as SalesAmount
        ,sum(sum(f.SalesAmount) )
                over(partition by f.productkey, year(f.orderdate)
                order by f.productkey, year(f.orderdate), f.orderdate
                rows between unbounded preceding and current row
            ) as SalesAmtYTD
    from [dbo].[FactInternetSales]  as F
    group by f.orderdate
            ,f.ProductKey
            ,year(f.orderdate)
    order by f.ProductKey
            ,f.OrderDate

    Cheers,

    Clayton

    • Proposed as answer by SQLMonger Friday, November 8, 2013 4:43 PM
    Friday, November 8, 2013 4:43 PM