For each record I need the running total of minutes worked and the continuously worked time since the last break.
I get the running total by self joining the table:
Select a.ord, a.Start, a.End, a.minutes, a.break,
Sum(CASE WHEN b.break = 0 THEN b.minutes END) as RunningTotal
From table a
left outer join table b on a.ord > = b.ord
Group by a.ord, a.Start, a.End, a.minutes, a.break
ord Start End minutes Break RunningTotal RunningTotal_since_last_break
-------------------------------------------------- -----------------------------
1 08:15 12:00 225 0 225 225
2 12:00 12:15 15 1 225 0
3 12:15 15:00 165 0 390 165
4 15:00 19:00 240 0 630 405
So my question is: How can I get the Running Total since the last break?
Something like this - not really sure what you are looking for
with cte_1 as
( select start, minutes, num = (select count(*) from table b where b.break = 1 and b.start < a.start)
from table a
where break <> 1
)
select start, minutes = sum(b.minutes)
from cte_1 a
join cte_1 b
on a.num = b.num
and b.start <= a.start
group by a.start
Two things to consider if you are on SQL 2012 or later:
Use windowing function SUM(xx) OVER(xxx) to compute running total. What you posted would work as well, but you are reading from the table twice, so it is likely to be less efficient.
The Running total since last break is the type of problems classified as "islands and gaps". If you search the web for it, you will find a number of articles and methods for solving this type of problem. In particular, Ben-Gan's articles on the subject are very clear and easy to understand.
If you are on SQL 2012 or later, this is roughly how you would write the query. (I have only minimally tested it, so it might need work). The hardest part is creating the test data. So if you had posted the create and insert statements I have in the example below you probably would have gotten better and faster answers sooner.
If you are on an earlier version of SQL Server, there are other methods to get the same results, albeit less efficient and more complicated. Ben-Gan covers those methods as well in his writings.
DROP TABLE IF EXISTS #tmp;
CREATE TABLE #tmp
(ord INT, StartTime TIME, EndTime TIME, MinutesWorked int, isBreak BIT);
INSERT INTO #tmp VALUES
('1','08:15','12:00','225','0'),
('2','12:00','12:15','15','1'),
('3','12:15','15:00','165','0'),
('4','15:00','19:00','240','0');
SELECT *,
RuningTotalSince=SUM(CASE when IsBreak = 0 THEN MinutesWorked ELSE 0 END) over (PARTITION BY grp ORDER BY ord)
FROM
(
SELECT
*,
RunningTotal = SUM(CASE when IsBreak = 0 THEN MinutesWorked ELSE 0 END) OVER (ORDER BY ord),
grp = ROW_NUMBER() over (ORDER BY ord) - ROW_NUMBER() OVER (PARTITION BY IsBreak ORDER BY ord)
FROM
#tmp
)s
ORDER BY
ord;
Thank you for your answers, especially JamesK and no, I unfortunately have to use an older SQL version on that server. I will read the article on "islands and gaps".
I solved my problem finding first the last break for each line:
; with LB as (
Select a.ord, COALESCE(max(c.ord), a.ord) as LastBreak
from table a
left outer join (Select ord from table where break = 1) c on a.ord > c.ord and a.break = 0
group by a.ord))
and then joining it to the upper query:
Select a.ord, a.Start, a.End, a.minutes, a.break,
Sum(CASE WHEN b.break = 0 THEN b.minutes END) as RunningTotal,
Sum(CASE WHEN b.break = 0 and b.ord >= LP.LastBreak THEN b.minutes ELSE 0) as RunningTotal_since_last_break
From table a
left outer join table b on a.ord > = b.ord
left outer join LB on a.ord = LP.ord
Group by a.ord, a.Start, a.End, a.minutes, a.break
James, I could confirm your script on another server than I need the solution for: very compact!
This is what worked for me on SQL Server 2008
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
CREATE TABLE #tmp
(ord INT, StartTime TIME, EndTime TIME, MinutesWorked int, isBreak int);
INSERT INTO #tmp VALUES
('1','08:15','12:00','225',0),
('2','12:00','12:15','15',1),
('3','12:15','15:00','165',0),
('4','15:00','19:00','240',0);
; with LB as (
Select a.ord, COALESCE(max(c.ord), a.ord) as LastBreak
from #tmp a
left outer join (Select ord from #tmp where isBreak = 1) c on a.ord > c.ord and a.isBreak = 0
group by a.ord)
Select a.ord, a.StartTime, a.EndTime, a.MinutesWorked, a.isBreak,
Sum(CASE WHEN b.isBreak = 0 THEN b.MinutesWorked END) as RunningTotal,
Sum(CASE WHEN b.isBreak = 0 and b.ord >= LB.LastBreak THEN b.MinutesWorked ELSE 0 END) as RunningTotal_since_last_break
From #tmp a
left outer join #tmp b on a.ord > = b.ord
left outer join LB on a.ord = LB.ord
Group by a.ord, a.StartTime, a.EndTime, a.MinutesWorked, a.isBreak
