Privileging column value in joining tables with date between

Question

I have two tables and i try to join them with date between and to privilege a value column if two rows with same date are in table B, below an example:

Table A

id_1|id_2|Lib|date_1
1   | 1  |aa |2022-01-01
2   | 2  |bb |2022-02-01

Table B

id_1|Project|start_date|end_date|Typ
1   |dd     |2021-12-30|2022-01-20|abcd
1   |hh     |2021-12-30|2022-01-20|azert
2   |cc     |2022-01-30|2022-02-05|abcd
2   |cc     |2022-01-29|2022-02-11|dfgh
2   |cc     |2022-01-18|2022-02-19|azert

the excepting result is

id_1|id_2|Lib|date_1|Typ|Project|start_date
1   | 1  |aa |2022-01-01|hh|azert|2021-12-30
2   | 2  |bb |2022-02-01|cc|abcd|2022-01-30

So if table B contain multiple rows with same start_date and any of these rows contain 'azert' as value in column Typ this row is selected else ( no 'azert' values) we take the recent start_date without privileging.

From my side i did this query but i can not find how to take azert if it is in the row.

Select 
a.id_1
,a.id_2
,a.lib  
,a.date_1
,b.typ
,b.project
,b.start_date
from a
left join b
on a.id_1 = b.id_1
and date_1 between start_date and end_date

qualify 
row_number() over (partition by a.id_1,a.id_2,a.date_1 order by b.start_date desc ) = 1
;

this query select the typ = abcd for the id_1 instead of azert.

Any idea please! Thank you

Answer 1

Suggestion:

WITH CTE_RankB AS 
(
    SELECT
              b.*
              ,RANK() OVER(PARTITION BY b.id_1 ORDER BY b.start_date DESC, b.id_1) AS RANK
)

Select 
       a.id_1
       ,a.id_2
       ,a.lib  
       ,a.date_1
       ,COALESCE(AZERT.type,B.typ) AS 'typ'
       ,COALESCE(AZERT.project, B.project) AS 'project'
       ,COALESCE(AZERT.start_date, B.start_date) AS 'start_date'
from 
       a
 left join 
       b AS AZERT on AZERT.id_1 = a.id_1
      and b.typ = 'azert'
 left join
       CTE_RankB AS B on B.id_1 = a.id_1
       and B.RANK = 1

Basically you are creating a sub-set of b that contains azert and a subset of the first (most recent) b for each ID. The COALESCE() function will consume arguments until it finds one that is not null (you could use ISNULL() but that will only take two arguments; COALESCE is more flexible) So it will use the AZERT version if it exists, otherwise it will use the RANKed version.