Check value between multiple rows in SQL

Question

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

Answer 1

This will find the row with the matching range. I've done this example in sqlite3, so I've rearranged your dates to match the SQL date format.

CREATE TABLE one (
  id integer,
  date date
);

INSERT INTO one VALUES
(         101   ,   '2018-07-04'),
(         102   ,   '2018-11-15'),
(         103   ,   '2019-01-25'),
(         104   ,   '2019-06-28');

CREATE TABLE two (
    start date,
    end date,
    value varchar
);
INSERT INTO two VALUES
(        '2016-11-29',  '2017-11-28',  'A' ),
(        '2017-11-29',  '2018-11-29',  'B' ),
(        '2018-11-30',  '2019-03-15',  'C' ),
(        '2019-03-16',  '2019-06-29',  'D' ),
(        '2019-06-30',  '2021-11-31',  'E' );

SELECT o.id, t.value
FROM one o INNER JOIN two t ON o.date BETWEEN t.start AND t.end;

Output:

101|B
102|B
103|C
104|D

These answers do not match yours, but what you are asking for seems nonsensical. 2019-06-28 is clearly within the range for D, not for E. E doesn't start until two days later.

Followup

Here is sqlite3 code that rounds the start and end dates to the beginning of the month. This produces the results you want, but date functions vary from system to system.

SELECT o.id, t.value
FROM one o INNER JOIN two t ON 
    o.date BETWEEN DATE(t.start,"start of month") AND DATE(t.end, "start of month");

Output:

101|B
102|C
103|C
104|E

Answer 2

Making a shift to next row's value ( using Lead() Over() analytic function ) in case of the same year and month of a first table date compared to next row's start date in second table.
Tested with Oracle, Postgres and MariaDB ... may need adjustments for some other sql dialect ...

SELECT  id, val
FROM  ( Select      f.id, 
                    Case When EXTRACT(Year From f.a_date) = EXTRACT(Year From Coalesce(Lead(s.start_date) Over(Order By s.start_date), s.end_date)) And 
                              EXTRACT(Month From f.a_date) = EXTRACT(Month From Coalesce(Lead(s.start_date) Over(Order By s.start_date), s.end_date))
                         Then Coalesce(Lead(s.val) Over(Order By s.start_date), s.val)
                    Else s.val
                    End as val
        From        scnd s
        Left Join   fst f ON( f.a_date Between s.start_date And s.end_date )
      ) a
WHERE       id Is Not Null
ORDER BY    id

ID	VAL
101	B
102	C
103	C
104	E

fiddle