Joining tables based on datetime2 predicate

How do I join tables A and B (see pic) on TripID and where TimeTriggered is between SegmentStart and SegmentEnd?

I could try:

FROM TableA A
INNER JOIN TableB B
ON A.TripID = B.TripID
AND B.TimeTriggered BETWEEN A.SegmentStart AND A.SegmentEnd

However since BETWEEN is inclusive on both ends it would join alarm B to both segment 1 and 2.

I could try >= AND < but it would leave out alarm C. I could try > AND <= but it would leave out alarm A.

How to capture all 3 alarms without causing cartesian product for some?

Ideally the solution would also be index preserving.

[table objects]

Answer

You can arbitrarily choose one of them using apply:

SELECT . . . 
FROM TableA A CROSS APPLY
     (SELECT TOP (1) B.*
      FROM TableB B
      WHERE A.TripID = B.TripID AND
            B.TimeTriggered BETWEEN A.SegmentStart AND A.SegmentEnd
     ) B;

However, you should fix your data. Normally with datetime values and range, the first value is part of the range and the second is not.