August 20, 2021 at 11:28 am
Hello
Need help with sql script . sample data below with result expected in #data_result table.
I tried running the below script but it is bringing 2 rows for id 101 where as i needed the row where date_hr is 17:00 not the 16:00
select id, in_Datetime, date_hr from #data inner join #t_lead on (in_datetime between date_hr and n)
to remove the row with 16:00 I tried below query as well , but it is giving me row with 16:00 not the 17:00 one which needed
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)
regards
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
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
August 20, 2021 at 11:32 am
What's the condition on which you determine which hour to be returned ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 20, 2021 at 12:15 pm
Hi Johan
I need to compare the hr from in_datetime and date_hr whatever the hr for in_Datetime that id should show the hr from #t table.
hope that will make sense
regards
August 20, 2021 at 12:23 pm
Strip the MM:SS from the datetime using dateadd a couple times then join it to the HR date
Something like the below would do it
select
id,
in_datetime,
date_hr
from #data d
inner join #t t
on dateadd(hh, datediff(hh, 0, in_datetime), 0) = date_hr
order by id
August 20, 2021 at 12:25 pm
select *, dateadd(hour, datediff(hour, 0, in_datetime), 0) out_datetime
from #data;
[EDIT] Same answer as Ant-Green but 2 minutes later 🙂
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply