Check value between multiple rows in SQL

I need to write a query to output a table with 2 values – number & description.

Table 1 has 2 columns – id and date

         id        date
         101       04-07-2018
         102       15-11-2018
         103       25-01-2019
         104       28-06-2019

Table 2 has 3 columns – start date, end date and value

         start date    end date     value
         29-11-2016    28-11-2017   A
         29-11-2017    29-11-2018   B
         30-11-2018    15-03-2019   C
         16-03-2019    29-06-2019   D
         30-06-2019    31-11-2021   E

For id 101, 04-07-2018 falls between 29-11-2017 and 29-11-2018, value should be B. For 102, 15-11-2018 falls between 29-11-2017 and 29-11-2018, it starts with B, but later 11/2018 is updated in the next row 30-11-2018 and 15-03-2019, final value should be C instead of B.

I am trying to achieve the final table. Any suggestions would be appreciated.

          id      desc
          101     B
          102     C
          103     C
          104     E

You need to sign in to view this answers

Related Post