Query to mark if a value falls between a set of values

Related searches

I have two tables. And I want to check if a value from one, falls between any two numbers in the other table. For example the tables are:

table_a:
+--------+-------+---------------------+
|  name  | count |        time         |
+--------+-------+---------------------+
| table1 | 10    | 2019-05-03 10:30:00 |
| table1 | 20    | 2019-05-03 11:30:00 |
| table1 | 30    | 2019-05-03 12:30:00 |
| table1 | 40    | 2019-05-03 13:30:00 |
| table1 | 50    | 2019-05-03 14:30:00 |
+--------+-------+---------------------+

table_b:
+--------+-------+
|  name  | count |
+--------+-------+
| table1 | 35    |
+--------+-------+

From this I want to get the closest value above and below the number in table_b that exists in table_a, and then show this as a "match" and then also display the times of the closest value above and below this number. So the results should look something similar to:

Result:
+--------+--------------+---------------------+---------------------+
|  name  |    count     |     time_before     |     time_after      |
+--------+--------------+---------------------+---------------------+
| table1 | Counts Match | 2019-05-03 12:30:00 | 2019-05-03 13:30:00 |
+--------+--------------+---------------------+---------------------+

If this doesn't match, then it will show this in the results as not matching and the times will just be blank.

Thanks

You can do this by generating a table of before and after values for count and time in table_a, and then JOINing that to table_b such that table_b.count is between count_before and count_after:

SELECT a.name, 
       a.count_before || ',' || a.count_after  AS count,
       a.time_before,
       a.time_after
FROM (SELECT name,
             lag(count) over (order by time) AS count_before,
             count AS count_after,
             lag(time) over (order by time) AS time_before,
             time AS time_after
      FROM table_a) a
JOIN table_b b ON b.count BETWEEN a.count_before AND a.count_after

Output:

name    count   time_before                 time_after
table1  30,40   2019-05-03T12:30:00.000Z    2019-05-03T13:30:00.000Z

Demo on dbfiddle

Between Formula in Excel, Checking if a value falls between 2 other values is fairly common when you are What is Power Query and how to launch it from Excel or Power BI? This helped me figure out the basis to a formula to tell me if a number was between a set of numbers OR if it Mark Jones - bonus target % on 02/05/2015 Between Formula in Excel for Numbers: Lets say you have 3 values in A1, A2 and A3. And you want to find out if A1 falls between A2 and A3. Now, the simplest formula for such a thing would be test whether the conditions A1>=A2, A1<=A3 are both true.

You can generate the list of start/end counts (and timestamps) using a window function, then join that to the second table:

with ranges as ( 
  select name, 
         "time" as time_start,
         "count" as count_start, 
         lead("count") over w as count_end, 
         lead("time") over w as time_end
  from table_a
  window w as (partition by name order by "time")
)
select t2.name, t2."count", r.count_start, r.count_end, r.time_start, r.time_end
from table_b t2 
  join ranges r on r.name = t2.name and t2."count" between r.count_start and r.count_end;

Online example: https://rextester.com/MBYZYU33789

BETWEEN Formula in Excel, Our goal is to evaluate if a given value is between a range, for example, is 6 between 1 OPTION 1:Using a combination of MIN, MAX & AND function The function that can be used to determine if the value in cell D7 is in-between values in cell A7 & B7 is 50 Things You Can Do With Excel Power Query. To test if a numeric value falls between two numbers, you can use the AND function with two logical tests. In the example shown, the formula in E5 is: =AND(D5>MIN(B5,C5),D5MIN(B5,C5) // is D5 greater than smaller? =D5=MIN(B5,C5),D5

One method is a lateral join:

select b.*, a.lower, a.upper
from table_b b cross join lateral
     (select max(lower) as lower, max(upper) as upper
      from ((select a.time as lower, null as upper
             from table_a a
             where a.value <= b.value
             order by b.time desc
             fetch first 1 row only
            ) union all
            (select null as lower, a.time as upper
             from table_a a
             where a.value >= b.value
             order by b.time asc
             fetch first 1 row only
            )
           ) a
      ) a

VLOOKUP Hack #2: Range Lookups, When the 4th argument is TRUE, or omitted, it tells Excel to perform a It means you are looking for a value between a range of values. Then, it would be easy to have VLOOKUP retrieve the corresponding quarter label for a set of since you could receive a potentially false “match” if your query value´┐Ż Hi all, In one table I have the Age of IDcontacts, and in another table I have AgeRanges such as : Id AgeFrom AgeTo Description 1 0 10 0-10 2 11 20 11-20 3 21 30 21-30 4 31 40 31-40 5 41 50 41-50 6 51 60 51-60 7 61 70 61-70 8 71 80 71-80 9 81 90 81-90 10 91 200 > 90 I would like to connect b

Excel IF statement with multiple AND/OR conditions, nested IF , The formula tells Excel to return "Pass" if a value in column C >=20 You use the combination of IF & OR functions in a similar way. Column E will have the " Pass" mark if either the first score is equal to or -If cells A1:Z1 ALL have numeric values between 121 and 124 the result should read "Hold Time" We can create a comparative statement in a grid view by using SQL IF and between command. SELECT class, SUM ( IF ( mark <50, 1, 0 ) ) AS grade_C, SUM ( IF ( mark BETWEEN 50 AND 70 , 1, 0 ) ) AS grade_B, SUM ( IF ( mark >70, 1, 0 ) ) AS grade_A FROM `student` GROUP BY class. Out put is here.

Selecting Value Between a Range of Values Using SQL BETWEEN , Selecting Data Within a Range of Values with SQL BETWEEN Operator The BETWEEN operator is used in the WHERE clause to select a value within a If you pass the NULL values to the BETWEEN operator e.g., expr the same result set, however, the query that uses the BETWEEN operator is much more readable. Using IF doesn't work, because that only looks to see if C2 >A2 and <B2, C3 > A3 and < B3 etc. I want to see if each value listed in column C is between any of the paired values listed in A and B, which is a thousand or so rows.

Hello Everyone, I have a DAX formula which Is not giving me the results required. Would appreciate your assistance. (If a value is between the below different criteria valuesshow it otherwise return a blank).

Comments
  • Thanks, this is perfect
  • @alwaystrying no worries. I'm glad I could help.