Look up value in other table for range and do the calculation according to the range-CodePudding

I am trying to come up with T-SQL query where it checks the date in one table (Stage), and then look up other table (DIM) for where date range resides, then do the calculation based on range.

This is Stage table (data type is nvarchar (255)).

punch_start
Mar 29 2022  7:00AM
Mar 23 2022 10:28PM
Apr 11 2022  7:12AM
Apr  5 2022  2:18PM
Mar 30 2022 11:00AM
Apr  7 2022  6:57AM
Apr  7 2022 12:00AM
Mar 23 2022  6:44AM
Mar 24 2022  4:00PM
Apr 14 2022 11:18AM
Apr  2 2022  1:34PM

I need to apply whether it is -4 or -5 based on where Stage.punch_start locates between (inclusive):

This is Dim table (data type is DATE).

start_dt    end_dt      hour_diff
2022-01-22  2022-03-12  -5
2022-03-13  2022-03-30  -4
2022-03-31  2022-04-15  -5
2022-04-16  2023-11-04  -4

How do I join the relationship and apply (add) -4 or -5 (hour_diff) to the punch_start in the Stage table?

So, for instance, if punch_start is '2022-03-20', it adds -4.

If punch_start is '2022-04-03', it adds -5.

I think I know how to change from NVARCHAR to DATE format and do the addition (like TRY_CONVERT(DATE, punch_start, 104)), but I am not sure how the logic should be to get the date range.

Here is one select statement where I could display the original data type (nvarchar 255) into DATE.

select distinct TRY_CONVERT(DATE, punch_start, 104) as punch_start from [dbo].[Stage] order by punch_start asc

CodePudding user response：

It's not clear what you're expecting as a result, but does the following give you enough to go on?

select punch_start, DateAdd(hour,hour_diff,ps) newDate
from stage s
cross apply(values(Try_Convert(datetime,punch_start)))x(ps)
left join dim d on ps between d.start_dt and d.end_dt;