CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functions
Yen
Yen

Reputation: 23

SQL dynamic running total

I'm hoping somebody can assist me with a query, I need to build a running total that goes up and down, this is rough idea of the data set

poolname  date                     status
test1     2018-11-01 08:39:09.737  started
test1     2018-11-01 08:40:09.737  started
test1     2018-11-01 08:41:09.737  finished
test1     2018-11-01 08:42:09.737  started
test1     2018-11-01 08:44:09.737  finished
test1     2018-11-01 08:45:09.737  finished
test2     2018-11-01 08:21:09.737  started
test2     2018-11-01 08:22:09.737  started
test2     2018-11-01 08:24:09.737  finished
test2     2018-11-01 08:30:09.737  finished

This is the output I need:

poolname  date                     status     RunningTotal
test1     2018-11-01 08:39:09.737  started    1
test1     2018-11-01 08:40:09.737  started    2
test1     2018-11-01 08:41:09.737  finished   1
test1     2018-11-01 08:42:09.737  started    2
test1     2018-11-01 08:44:09.737  finished   1
test1     2018-11-01 08:45:09.737  finished   0
test2     2018-11-01 08:21:09.737  started    1
test2     2018-11-01 08:22:09.737  started    2
test2     2018-11-01 08:24:09.737  finished   1
test2     2018-11-01 08:30:09.737  finished   0

I hope this make sense, I need to keep it as efficient as possible, I've been attempting to use window functions, LEAD and LAG. Im sure there is a really easy way to do this but I've not figured it out yet.

Upvotes: 1

Views: 174

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You want a cumulative sum:

select t.*,
       sum(case when status = 'start' then 1
                when status = 'finished' then -1
                else 0
           end) over (partition by poolid order by date) as as runningTotal
from t;

Upvotes: 1

Related Questions