-2

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]

1

eshirvana
  • 23,227
  • 3
  • 22
  • 38
thesnowplow
  • 3
  • 1
  • 2
    Seems like either you want `>=` and `<` or `>` and `<=` logic, or your data is wrong. Clearly either section 2 connects to 2 other rows of or one of the other rows doesn't have a relationship with the others. – Thom A Jun 06 '21 at 17:47
  • so alram B ,should goes with each segment and why? or you don't mind either – eshirvana Jun 06 '21 at 17:47
  • however as Larnu mentioned , your data is not correct, the best solution would be to fix your data – eshirvana Jun 06 '21 at 17:49
  • As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Jun 07 '21 at 08:52

1 Answers1

0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I'm not sure I'm understanding how to fix the data. Unfortunately there is no telling whether Alarm B should belong to segment 1 or 2 as there's no SegmentID in the Table B to explicitly join by. If I would use >= and < then Alarm C would not be matched to any segments. – thesnowplow Jun 07 '21 at 15:34
  • @thesnowplow . . . The issue is the start and end times. You should have a structure where the end of the interval is not part of the interval. – Gordon Linoff Jun 07 '21 at 15:36
  • I think I'm too dense to understand this. The end time of one segment is the start time of the next segment. What you're suggesting is that this should not be the case and there should be a small unit increment between when one segment ends and the next one starts? – thesnowplow Jun 07 '21 at 15:48
  • @thesnowplow . . . Exactly. That is the point. The end time for a segment should not be part of that segment. It should be associated with the *next* segment. – Gordon Linoff Jun 07 '21 at 15:50
  • Okay thanks a lot for explaining this. I will look into how our data is structured in the source & will ask our data architect. I will mark your answer as correct as it got me some level of resolution. – thesnowplow Jun 07 '21 at 16:01
  • 1
    Btw I just realized that your name seems familiar.. looked over at my bookshelf and it's on my copy of 'Data Analysis Using SQL and Excel (Seconds Edition)' that I picked up a few years ago. Amazing stuff.. thanks a lot! – thesnowplow Jun 07 '21 at 16:09