get-a-column-latest-value-on-first, Sql Server

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

CREATE TABLE d
    ([id] int, [col2] varchar(8), [modified_date] date)
;

INSERT INTO d
    ([id], [col2], [modified_date] )
VALUES
    (1, 'red', '20190107'),
    (1, 'green', '20190207'),
    (1, 'blue', '20190307'),
    (2, 'green', '20190112'),
    (2, 'blue', '20190302'),
    (2, 'red', '20190419'),
    (3, 'red', '20181212'),
    (3, 'green', '20190210')
;

CREATE TABLE y
    ([FOM] date)
;

INSERT INTO y
    ([FOM])
VALUES
    ('20190101'),
    ('20190201'),
    ('20190301'),
    ('20190401'),
    ('20190501'),
    ('20190601'),
    ('20190701'),
    ('20190801'),
    ('20190901'),
    ('20191001'),
    ('20191101'),
    ('20191201')
;

WITH 
data AS
(
    SELECT *, 
        DATEADD(d, 1, EOMONTH(modified_date)) AS FirstOfNextMonth,
        RANK() OVER (
            PARTITION BY id, DATEADD(d, 1, EOMONTH(modified_date))
            ORDER BY modified_date DESC
            ) AS rn
    FROM d
),
m_id AS 
(
    SELECT * 
    FROM y, (SELECT DISTINCT id from d) as p
)

SELECT m_id.id, m_id.FOM, latest.col2 
FROM m_id LEFT JOIN
    (
        SELECT * FROM data
        WHERE rn = 1
    ) AS latest
ON m_id.FOM = latest.FirstOfNextMonth AND m_id.id = latest.id
WHERE m_id.FOM < '20190501'

View schema
Execution time: 0,03 sec, rows selected: 12, rows affected: 20, absolute service time: 0,22 sec, absolute service time: 0,22 sec