question

leodec-4354 avatar image
0 Votes"
leodec-4354 asked Viorel-1 edited

Help with sql script

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


sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

Check two queries:

 select id, in_datetime,
     (select max(date_hr) from #t where date_hr <= d.in_datetime) hr
 from #data d
 order by id
    
 select id, in_datetime, 
     datetimefromparts(year(in_datetime), month(in_datetime), day(in_datetime), datepart(hour, in_datetime), 0, 0, 0) hr
 from #data
 order by id

Or explain the right logic.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.