Continuous columns sum based on another column in sql server

user_10650496 0
Viewed 30

I have data like below

AppName Start Finish Totaltime
MS Word 2021-02-28 7:30:25 2021-02-28 7:31:25 1
MS Word 2021-02-28 7:31:26 2021-02-28 7:33:25 2
MS Word 2021-02-28 7:33:27 2021-02-28 7:35:25 2
MS Word 2021-02-28 7:35:28 2021-02-28 7:37:25 2
Google Chrome 2021-02-28 7:37:29 2021-02-28 7:39:25 2
Windows Explorer 2021-02-28 7:39:30 2021-02-28 7:41:25 2
Windows Explorer 2021-02-28 7:41:31 2021-02-28 7:43:25 2
MS Word 2021-02-28 7:43:32 2021-02-28 7:45:25 2
MS Word 2021-02-28 7:45:33 2021-02-28 7:47:25 2
Google Chrome 2021-02-28 7:47:34 2021-02-28 7:49:25 2
Google Chrome 2021-02-28 7:49:35 2021-02-28 7:51:25 2
Google Chrome 2021-02-28 7:51:36 2021-02-28 7:53:25 2
Microsoft Excel 2021-02-28 7:53:37 2021-02-28 7:55:25 2

and need the coninuoues column Totaltime sum based on App name

output something like this

AppName Start Finish Totaltime
MS Word 2021-02-28 7:30:25 2021-02-28 7:37:25 7
Google Chrome 2021-02-28 7:37:29 2021-02-28 7:39:25 2
Windows Explorer 2021-02-28 7:39:30 2021-02-28 7:43:25 4
MS Word 2021-02-28 7:43:32 2021-02-28 7:47:25 4
Google Chrome 2021-02-28 7:47:34 2021-02-28 7:53:25 6
Microsoft Excel 2021-02-28 7:53:37 2021-02-28 7:55:25 2
sql-server sql
1 Answers

This is a type of gaps and islands problem. You don't care about the time gaps in the table, so you can use the difference of row numbers:

select appname, min(start), max(finish), sum(time)
from (select t.*,
             row_number() over (order by start) as seqnum,
             row_number() over (partition by appname order by start) as seqnum_a
      from t
     ) t
group by appname, (seqnum - seqnum_a)
user_11440350
Related