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

get-a-column-latest-value-on-first

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

View schema
Execution time: 0,02 sec, rows selected: 36, rows affected: 20, absolute service time: 0,2 sec, absolute service time: 0,21 sec

	id	FOM	col2
1	1	01.01.2019 00:00:00	NULL
2	1	01.02.2019 00:00:00	red
3	1	01.03.2019 00:00:00	green
4	1	01.04.2019 00:00:00	blue
5	1	01.05.2019 00:00:00	NULL
6	1	01.06.2019 00:00:00	NULL
7	1	01.07.2019 00:00:00	NULL
8	1	01.08.2019 00:00:00	NULL
9	1	01.09.2019 00:00:00	NULL
10	1	01.10.2019 00:00:00	NULL
11	1	01.11.2019 00:00:00	NULL
12	1	01.12.2019 00:00:00	NULL
13	2	01.01.2019 00:00:00	NULL
14	2	01.02.2019 00:00:00	green
15	2	01.03.2019 00:00:00	NULL
16	2	01.04.2019 00:00:00	blue
17	2	01.05.2019 00:00:00	red
18	2	01.06.2019 00:00:00	NULL
19	2	01.07.2019 00:00:00	NULL
20	2	01.08.2019 00:00:00	NULL
21	2	01.09.2019 00:00:00	NULL
22	2	01.10.2019 00:00:00	NULL
23	2	01.11.2019 00:00:00	NULL
24	2	01.12.2019 00:00:00	NULL
25	3	01.01.2019 00:00:00	red
26	3	01.02.2019 00:00:00	NULL
27	3	01.03.2019 00:00:00	green
28	3	01.04.2019 00:00:00	NULL
29	3	01.05.2019 00:00:00	NULL
30	3	01.06.2019 00:00:00	NULL
31	3	01.07.2019 00:00:00	NULL
32	3	01.08.2019 00:00:00	NULL
33	3	01.09.2019 00:00:00	NULL
34	3	01.10.2019 00:00:00	NULL
35	3	01.11.2019 00:00:00	NULL
36	3	01.12.2019 00:00:00	NULL