Timestamp in Between Join SQL Server-CodePudding

How do I join Table B on Table A where Table B has timestamps in-between Table A's timestamps? I am using SQL Server 2008.

Table A

Start               End
---------------------------------------
2022-04-29 15:00:00 2022-04-29 15:30:00
2022-04-29 15:30:00 2022-04-29 17:00:00
2022-04-29 17:00:00 2022-04-29 18:00:00
2022-04-29 18:00:00 2022-04-29 18:30:00
2022-04-29 18:30:00 2022-04-29 20:00:00

Table B

Start             Value
-----------------------
2022-04-29 12:00:00 100
2022-04-29 15:30:00 200
2022-04-29 16:00:00 300
2022-04-29 18:00:00 400
2022-04-29 21:00:00 500

Desired result - product of Table A and B:

Start               End                Value
--------------------------------------------
2022-04-29 15:00:00 2022-04-29 15:30:00 200
2022-04-29 15:30:00 2022-04-29 17:00:00 300
2022-04-29 17:00:00 2022-04-29 18:00:00 NaN
2022-04-29 18:00:00 2022-04-29 18:30:00 400
2022-04-29 18:30:00 2022-04-29 20:00:00 NaN

CodePudding user response：

You really need to decide on whether or not the top ranges are inclusive or not (see Larnu's comment) >= and < will generate different results than > and <=

Select A.*
      ,B.Value
 From TableA A
 Left Join TableB B on B.Start  >= A.Start
                    and B.Start <  A.[End]

Results