Running total per row for column value in SQL Server

Question

I have a query that returns DateTime Spans an employee has worked for a day, what week of the year that time relates to and the date of the spans. It works really well, but I want to calculate a running total by day for each day in a particular week.

Currently, my query looks like this:

SELECT A.*
FROM (
    SELECT WeekNumber, SUM(NumberOfSecondsWorked) AS NumberOfSecondsWorkedInDay, MIN(FromTime) AS StartOfDay, MAX(ToTime) AS EndOfDay, (SELECT WeeklyTotal = SUM(NumberOfSecondsWorked) FROM #temp AS T2 WHERE T2.WeekNumber = T1.WeekNumber) AS WeeklyTotal
    FROM #temp AS T1 GROUP BY DayMonth, WeekNumber) A

And returns this:

The issue I'm having is I would like the Weekly total to be summed by Row for each week, so the data would look like the following:

Where the weekly total sums up for each row. Can anyone tell me what I'm doing wrong in my query?

Possible duplicate of [Calculate running total / running balance](https://stackoverflow.com/questions/11310877/calculate-running-total-running-balance) — Han, Jul 05 '19 at 17:06

Gordon Linoff · Accepted Answer · 2019-07-08T15:12:26.980

Your query and your result set are not really consistent. Your result set has StartOfDay for instance.

But, you seem to want:

SELECT WeekNumber, SUM(NumberOfSecondsWorked) AS NumberOfSecondsWorkedInDay,
       MIN(FromTime) AS StartOfDay, MAX(ToTime) AS EndOfDay,
       SUM(SUM(NumberOfSecondsWorked)) OVER (PARTITION BY WeekNumber ORDER BY MIN(FromTime))
FROM #temp AS T1
GROUP BY DayMonth, WeekNumber;

EDIT:

In SQL Server 2008, you need a different approach such as a correlated subquery:

WITH wn as (
      SELECT WeekNumber, SUM(NumberOfSecondsWorked) AS NumberOfSecondsWorkedInDay,
             MIN(FromTime) AS StartOfDay, MAX(ToTime) AS EndOfDay
      FROM #temp T1
      GROUP BY DayMonth, WeekNumber
     )
SELECT wn.*,
       (SELECT SUM(wn2.NumberOfSecondsWorked)
        FROM wn wn2
        WHERE wn2.WeekNumber = wn.WeekNumber AND
              wn2.FromTime <= wn.FromTime
       )
FROM wn;

I seem to have to ORDER BY MIN(FromTime) AS StartOfDay is declared in the same select statement, is this correct? — user1501171, Jul 05 '19 at 17:15
Is it possible do this in SQL Server 2008 for this query? Thanks! — user1501171, Jul 08 '19 at 14:40

score 1 · Answer 2 · answered Jul 05 '19 at 17:08

try like below by using window function

     SELECT WeekNumber,
     SUM(NumberOfSecondsWorked) AS NumberOfSecondsWorkedInDay,
     MIN(FromTime) AS StartOfDay, MAX(ToTime) AS EndOfDay,
     SUM(NumberOfSecondsWorked) over( order by WeekNumber) AS WeeklyTotal
    FROM #temp AS T1
    GROUP BY DayMonth, WeekNumber

Running total per row for column value in SQL Server

2 Answers2