LOOKUP VALUE BETWEEN DATES AND MULTIPLE CONDITIONS
Hello!! This may be already posted, but can't find it or related. I have 2 tables, a master table like this: KEY BND SEC START_HOUR END_HOUR REPS 8 S2 18/04/23 5:00 pm 19/04/23 2:00 am REPS 8 S1 18/04/23 6:00 am 18/04/23 5:00 pm ZATR 8 S3 18/04/23 9:00 pm 19/04/23 4:00 am ZATR 8 S1 18/04/23 8:00 am 18/04/23 7:00 pm MAKI 3 S2 18/04/23 9:00 pm 19/04/23 4:00 am QWER 2 S3 18/04/23 5:30 am 18/04/23 1:30 pm ZATR 3 S2 18/04/23 10:00 pm 19/04/23 5:00 am And values table: KEY BND HOUR ACT REPS 8 18/04/23 5:35 pm REPS 8 18/04/23 6:34 am ZATR 3 19/04/23 2:30 am This is only a sample but imagine I have hundreds of rows; many KEY and BND values, and start and end hour changes for any combination of them. This means that for REPS code I can have SEC with different Start and End hours. (SEC only have values from S1 to S3). I can even have 30 mins in hour schedule, as you can see in the last row. Master table never changes, is a master at the end, Values table shows what happened at specific time, so I need to get that SEC value. I need to add a column with the vale from column SEC to values table according to CODE and LINE columns, but that is between the Start and End HOURS. An expected result value would be: KEY BND HOUR ACT SEC REPS 8 18/04/23 5:35 pm S2 REPS 8 18/04/23 6:34 am S1 ZATR 3 19/04/23 2:30 am S2 As you can see, KEY = RESP and BND = 8, so at HOUR=18/04/23 5:35 pm some value was stored in ACT column (not important for this query). So for this HOUR, we go to the master table where for those KEY & BND values, HOUR is between START_HOUR and END_HOUR where SEC = S2, so I bring S2. And so. I hope I'd clear. Thanks in advance.