6

I have a view like this:

col_1  col_2  my_date
-----  -----  -------
1      5      2011
2      6      2014
3      7      2012
4      8      2011

And a table like this:

date_1  date_2  the_value
------  ------  ---------
2010    2012    v1
2013    2015    v2

I want something like the Excel VLOOKUP function that find the value (the_value) which my_date is between date_1 and date_2, so I can have a result like this:

col_1  col_2  my_date  the_value
-----  -----  -------  ---------
1      5      2011     v1
2      6      2014     v2
3      7      2012     v1
4      8      2011     v1

The type of date columns are DATE. These are sample data for simplicity.

Vahid
  • 3,384
  • 2
  • 35
  • 69
  • Why don't you use between clause by joining these two tables? – Tharunkumar Reddy Nov 07 '16 at 08:37
  • You can join these two table on "my_date between date_1 and date_2", however that can duplicate your rows. If you want the exact functioning of vlookup, you can group by the columns in your first table and use First_Value(the_value) did you try that? – Emil Holub Nov 07 '16 at 08:39

2 Answers2

8

That is a join in SQL with a between rather than an equality join condition. 

select t1.col_1, t1.col_2, t1.my_date, t2.the_value
from table_one t1
  join table_two t2 on t1.my_date between t2.date_1 and t2.date_2;

Note that between includes the boundaries so it would also return rows where my_date is 2010. If you don't want that you need to use a join condition with > and <:

select t1.col_1, t1.col_2, t1.my_date, t2.the_value
from table_one t1
  join table_two t2 on t1.my_date > t2.date_1 
                   and t1.my_date < t2.date_2;

This also requires your "date" ranges to be non-overlapping, otherwise you'd get some strange results.

2

You can use a correlated query to fetch the value: 

SELECT t.*,
       (SELECT s.the_value FROM t2 s
        WHERE t.my_date between s.date_1 and s.date_2) as the_value
FROM t1 t
sagi
  • 40,026
  • 6
  • 59
  • 84