CODEWITHSUNDEEP
sqlsql-server
Row5
Row5

Reputation: 11

Getting Min and Max of a status change

I have a table that logs a status every second. I need to grab the start and stop of a status change every change, and a count of how many times it changes. The status constantly repeat through the table (status 0 through status 5) through the day.

So my output will be:

1, 2018-08-28 5:20:35, 2018-08-28 5:20:36
0, 2018-08-28 5:21:05, 2018-08-28 5:21:36
4, 2018-08-28 5:22:05, 2018-08-28 5:23:06
0, 2018-08-28 5:23:35, 2018-08-28 5:23:35 
1, 2018-08-28 5:23:36, 2018-08-28 5:24:05



t_stamp,             status
2018-08-28 5:20:35,     1
2018-08-28 5:20:36,     1
2018-08-28 5:21:05,     0
2018-08-28 5:21:06,     0
2018-08-28 5:21:35,     0
2018-08-28 5:21:36,     0
2018-08-28 5:22:05,     4
2018-08-28 5:22:06,     4
2018-08-28 5:22:35,     4
2018-08-28 5:22:36,     4
2018-08-28 5:23:05,     4
2018-08-28 5:23:06,     4
2018-08-28 5:23:35,     0
2018-08-28 5:23:36,     1
2018-08-28 5:24:05,     1

Select Status ,
       MIN(t_stamp) 'Min_Time',
       Max(t_stamp) 'Max_time'  
From test_table  
group by Status, t_stamp

Upvotes: 1

Views: 282

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You need differences of row_numbers :

SELECT status, MIN(t_stamp), MAX(t_stamp)
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY t_stamp) AS seq1,
                ROW_NUMBER() OVER (PARTITION BY status ORDER BY t_stamp) AS seq2
      FROM table t
     ) t
GROUP BY status, (seq1- seq2);

Upvotes: 1

Related Questions