Hi
Need help with sql script. See the below test data
create table #t (date_hr datetime)
insert into #t values ('2021-01-01 00:00:00')
insert into #t values ('2021-01-01 01:00:00')
insert into #t values ('2021-01-01 02:00:00')
insert into #t values ('2021-01-01 03:00:00')
insert into #t values ('2021-01-01 04:00:00')
insert into #t values ('2021-01-01 05:00:00')
insert into #t values ('2021-01-01 06:00:00')
insert into #t values ('2021-01-01 07:00:00')
insert into #t values ('2021-01-01 08:00:00')
insert into #t values ('2021-01-01 09:00:00')
insert into #t values ('2021-01-01 10:00:00')
insert into #t values ('2021-01-01 11:00:00')
insert into #t values ('2021-01-01 12:00:00')
insert into #t values ('2021-01-01 13:00:00')
insert into #t values ('2021-01-01 14:00:00')
insert into #t values ('2021-01-01 15:00:00')
insert into #t values ('2021-01-01 16:00:00')
insert into #t values ('2021-01-01 17:00:00')
insert into #t values ('2021-01-01 18:00:00')
insert into #t values ('2021-01-01 19:00:00')
insert into #t values ('2021-01-01 20:00:00')
insert into #t values ('2021-01-01 21:00:00')
insert into #t values ('2021-01-01 22:00:00')
insert into #t values ('2021-01-01 23:00:00')
select * From #t
select lead (date_hr) OVER(ORDER BY date_hr) n, * into #t_lead from #t
create table #data (id int , in_datetime datetime)
insert into #data values (101, '2021-01-01 17:00:00.000')
insert into #data values (102, '2021-01-01 02:48:00.000')
insert into #data values (103, '2021-01-01 00:49:00.000')
insert into #data values (104, '2021-01-01 03:07:00.000')
select * From #data
I tried select id, in_Datetime, date_hr from #data inner join #t_lead on (in_datetime between date_hr and n)
the problem with this query is that I am getting 2 rows for id 101 where the time is round off.
then I tried select id, in_Datetime, date_hr from #data inner join #t_lead on (in_datetime between date_hr and n and in_datetime<>date_hr), this query take off 1 row but that is not correct one. see the result one what expected
Result required that create table #data_result (id int , in_datetime datetime, hr datetime) insert into #data_result values (101, '2021-01-01 17:00:00.000','2021-01-01 17:00:00.000') insert into #data_result values (102, '2021-01-01 02:48:00.000','2021-01-01 02:00:00.000') insert into #data_result values (103, '2021-01-01 00:49:00.000','2021-01-01 00:00:00.000') insert into #data_result values (104, '2021-01-01 03:07:00.000','2021-01-01 03:00:00.000') select * From #data_result