How can I reset SQL row numbers based on interval since row 1?

0
votes

I need to apply row numbers to a table and have them reset to 1 every two hours based on the DateTime column value for row 1. Quite hard to explain, below is an example of what I'm trying to achieve - any ideas?

DateTime              Row Number
-----------           -----------
23/03/2021 12:14:59   1
23/03/2021 12:47:53   2
23/03/2021 13:24:05   3
23/03/2021 14:00:26   4
23/03/2021 14:03:27   5
23/03/2021 14:12:14   6
23/03/2021 14:14:56   7
23/03/2021 14:30:15   1
23/03/2021 15:51:28   2
23/03/2021 16:17:34   3
23/03/2021 16:22:12   4
23/03/2021 17:22:42   1

Any help would be appreciated, I'm going crazy trying to figure this out. Thanks!

1
sqlsql-serverrow-number

1 Answers

2
votes

There's no worthwhile way to do this as you insert rows. But you can easily calculate this in a query, something like:

drop table if exists #t;

set dateformat dmy;
create table #t(dt datetime)
insert into #t(dt) values
('23/03/2021 16:56:40'),  
('23/03/2021 12:14:59'),  
('23/03/2021 12:47:53'),  
('23/03/2021 13:24:05'),  
('23/03/2021 14:00:26'),  
('23/03/2021 14:03:27'),  
('23/03/2021 14:12:14'),  
('23/03/2021 14:14:56'),  
('23/03/2021 14:30:15'),  
('23/03/2021 15:51:28'),  
('23/03/2021 16:17:34'),  
('23/03/2021 16:22:12'),  
('23/03/2021 17:22:42');
    
select dt, row_number() over (partition by datediff(hour,cast(dt as date),dt)/2 order by dt) w
from #t
order by dt;

outputs

dt                      w
----------------------- --------------------
2021-03-23 12:14:59.000 1
2021-03-23 12:47:53.000 2
2021-03-23 13:24:05.000 3
2021-03-23 14:00:26.000 1
2021-03-23 14:03:27.000 2
2021-03-23 14:12:14.000 3
2021-03-23 14:14:56.000 4
2021-03-23 14:30:15.000 5
2021-03-23 15:51:28.000 6
2021-03-23 16:17:34.000 1
2021-03-23 16:22:12.000 2
2021-03-23 16:56:40.000 3
2021-03-23 17:22:42.000 4