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