Match the date gaps

Transact-SQL
1

I need a bit of assistance and sorry if I cannot explain that well. I need the #DATE table updated with values from #COLOR table per the matching START_DATE and END_DATE. The issue is there are gaps in dates and also multiple rows in the #COLOR table that can fulfill the values in the #DATE table. If this happens, I would want to take the later START_DATE from the #COLOR table.

--==== Create some sample data
CREATE TABLE #DATE
(ID VARCHAR(5), BEGIN_DATE VARCHAR(8), END_DATE VARCHAR(8),COLOR_DATE VARCHAR(8),COLOR VARCHAR(10))

CREATE TABLE #COLOR
(ID VARCHAR(5), BEGIN_DATE VARCHAR(8), END_DATE VARCHAR(8),COLOR VARCHAR(10))

INSERT INTO #DATE (ID,BEGIN_DATE,END_DATE,COLOR_DATE,COLOR)
VALUES ('1','20150101','20300101',NULL,NULL)
,('2','20150101','20150101',NULL,NULL)
,('2','20150101','20190131',NULL,NULL)
,('2','20190201','20200101',NULL,NULL)

INSERT INTO #COLOR (ID,BEGIN_DATE,END_DATE,COLOR)
VALUES ('1','20150101','20160101','RED')
,('1','20170101','20180101','BLUE')
,('1','20190101','20240131','ORANGE')
,('2','20150101','20190131','BLUE')
,('2','20190201','20200101','GREEN')

--==== Update does not have the correct value for ID-1, it should be ORANGE with 20190101 COLOR_DATE
--==== I want to take the later date if there is an overlap
UPDATE DATE_TABLE
SET DATE_TABLE.COLOR=COLOR_TABLE.COLOR 
,DATE_TABLE.COLOR_DATE=COLOR_TABLE.BEGIN_DATE
FROM #DATE DATE_TABLE
INNER JOIN #COLOR COLOR_TABLE
ON COLOR_TABLE.ID = DATE_TABLE.ID 
AND (DATE_TABLE.BEGIN_DATE BETWEEN COLOR_TABLE.BEGIN_DATE AND COLOR_TABLE.END_DATE)


--==== Value for ID-1 should be orange, it should be ORANGE with 20190101 COLOR_DATE
--==== Update did not fulfill ID-2-20150101-20150101 but it did update the other ID-2 fields correctly
UPDATE DATE_TABLE
SET DATE_TABLE.COLOR=COLOR_TABLE.COLOR 
,DATE_TABLE.COLOR_DATE=COLOR_TABLE.BEGIN_DATE
FROM #DATE DATE_TABLE
INNER JOIN #COLOR COLOR_TABLE
ON COLOR_TABLE.ID = DATE_TABLE.ID 
AND COLOR_TABLE.END_DATE BETWEEN DATE_TABLE.BEGIN_DATE AND ISNULL(DATE_TABLE.END_DATE,'30000101')

SELECT * FROM #COLOR ORDER BY ID,BEGIN_DATE,END_DATE
SELECT * FROM #DATE ORDER BY ID,BEGIN_DATE,END_DATE
2 
;with cte as (
select d.id,
		 C.COLOR 
	  ,C.BEGIN_DATE
	  ,row_number() over (partition by d.id, d.begin_date order by c.begin_date desc) as rownum
FROM #DATE D
INNER JOIN #COLOR C
ON C.ID = D.ID 
AND d.BEGIN_DATE <= c.end_DATE
and d.end_date >= c.Begin_date)

update d 
   set color_date = c.BEGIN_DATE,
	   color = c.color
 from #date d
	join cte c 
	on c.id = d.id
	and c.rownum = 1
3

I think this will do it:

UPDATE DATE_TABLE
SET DATE_TABLE.COLOR=COLOR_TABLE.COLOR 
,DATE_TABLE.COLOR_DATE=COLOR_TABLE.BEGIN_DATE
FROM #DATE DATE_TABLE
CROSS APPLY (
    SELECT TOP (1) *
    FROM #COLOR COLOR_TABLE
    WHERE COLOR_TABLE.ID = DATE_TABLE.ID 
        AND ((DATE_TABLE.BEGIN_DATE <= COLOR_TABLE.END_DATE 
        AND   DATE_TABLE.END_DATE >= COLOR_TABLE.BEGIN_DATE))
    ORDER BY COLOR_TABLE.BEGIN_DATE DESC
) AS COLOR_TABLE