Need help to create a SQL condition

Other SQL Server Topics
1

I have [shift_start_datetime], [shift_end_datetime], [trackout_datetime] and [work_week] columns in a table.

Basically I want to make a condition into my main query so that if [trackout_datetime] falls in between [shift_start_datetime] and [shift_end_datetime], it will show me which [work_week] that it is in

Sample table:

shift_start_datetime shift_end_datetime work_week
1 2022-11-16 07:00:00 2022-11-16 18:59:59 2022-46
2 2022-11-16 19:00:00 2022-11-17 06:59:59 2022-46
3 2022-11-17 07:00:00 2022-11-17 18:59:59 2022-46
4 2022-11-17 19:00:00 2022-11-18 06:59:59 2022-47

So let's say that my [trackout_datetime] is '2022-11-16 21:00:00', it falls under the second row of the sample table, so I should get something like this:

shift_start_datetime shift_end_datetime trackout_datetime work_week
1 2022-11-16 19:00:00 2022-11-17 06:59:59 2022-11-16 21:00:00 2022-46

where (flh.tracked_out_datetime >= e.shift_start_datetime and flh.tracked_out_datetime < e.shift_end_datetime) = e.[Work Week]

I tried something like this

2
create data script

drop table #Temp

declare @trackout_datetime datetime = '2022-11-16 21:00:00'

create table #Temp (id int , shift_start_datetime datetime, shift_end_datetime datetime,work_week varchar(100))

insert into #Temp select 1, '2022-11-16 07:00:00','2022-11-16 18:59:59','2022-46'
insert into #Temp select 2, '2022-11-16 19:00:00','2022-11-17 06:59:59','2022-46'
insert into #Temp select 3, '2022-11-17 07:00:00','2022-11-17 18:59:59','2022-46'
insert into #Temp select 4, '2022-11-17 19:00:00','2022-11-18 06:59:59','2022-47'

select * from #Temp


declare @trackout_datetime datetime = '2022-11-16 21:00:00' 

select 
   * 
from 
  #temp 
where 
  @trackout_datetime between shift_start_datetime and shift_end_datetime

image