Getting Min and Max of a status change
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
1 Answer
1
You need differences of row_numbers :
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);
Thank you very much! When I do this, I need to show the time stamp on every time it changes. This code I get high level distinct for the status. Am I misunderstanding?
– Row5
Aug 28 at 17:28
@Row5 You could put Yogesh's answer in a subquery and derive it like this LEAD(min_t_stamp) OVER(PARTITION BY 1 ORDER BY min_t_stamp) AS Change_T_Stamp The last one will be NULL.
– Error_2646
Aug 28 at 17:48
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Really nice approach
– Error_2646
Aug 28 at 17:20