CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012cumulative-sum
hurlski
hurlski

Reputation: 157

Cumulative sum based on consecutive values

I am using SQL Server 2012. I have a very large database containing over 40 million records for GPS tracks for mail delivery. I want to be able to figure out how long a letter carrier has been stopped for. So, when the carrier's distance is registered at 0, and every consecutive 0 following, I would like to know the cumulative time spent stopped. I would also like the summing to stop when anything higher than 0 is registered and to start again when a 0 is registered. This is what I have got so far:

SELECT
CASE
    WHEN Distance = 0 THEN SUM(Time_s) OVER (ORDER BY [ID] ROWS UNBOUNDED PRECEDING)  
    WHEN Distance <0 THEN NULL
END as StopTime

FROM data
ORDER BY ID

This is the result:

ID  Distance    Time_s  StopTime
1   6            899    NULL
2   0            789    1688
3   16           740    NULL
4   0            70     2498
5   0            50     2548
6   0            585    3133
7   0            10     3143
8   5            329    NULL
9   6            394    NULL
10  0            570    4436

I am clearly not telling the query to start the counting over again every time the consecutive zero's are interrupted by anything greater than zero. This is the result I want:

ID  Distance    Time_s  StopTime
1   6            899    NULL
2   0            789    789
3   16           740    NULL
4   0            70     70
5   0            50     130
6   0            585    715
7   0            10     725
8   5            329    NULL
9   6            394    NULL
10  0            570    570

Upvotes: 3

Views: 3228

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can define a group as the cumulative count of the non-zero values before any given value. Then you can use that group for the cumulative sum.

select d.id, d.distance, d.time_s,
       (case when d.distance = 0
             then sum(case when d.distance = 0 then d.time_s end) over (partition by grp order by id)
        end) as StopTime
from (select d.*,
             sum(case when distance = 0 then 0 else 1 end) over (order by id) as grp
      from data d
     ) d;

Upvotes: 2

Related Questions