SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE NODES (NodeId int,NodeDate DATETIME2,status NVARCHAR(128))
INSERT INTO NODES(NodeId,NodeDate,Status) VALUES 
(1,'2019/01/01','inactive'),
(1,'2019/03/01','inactive'),
(1,'2019/06/01','inactive'),
(1,'2019/09/01','inactive'),
(1,'2019/12/01','inactive'),
(2,'2019/01/01','inactive'),
(2,'2019/03/01','inactive'),
(2,'2019/06/01','inactive'),
(2,'2019/09/01','inactive'),
(2,'2019/12/01','inactive'),
(3,'2019/01/01','inactive'),
(3,'2019/03/01','inactive'),
(3,'2019/06/01','inactive'),
(3,'2019/09/01','inactive'),
(3,'2019/12/01','inactive')
15 rows affected

;WITH cte AS
(
   SELECT status,
         ROW_NUMBER() OVER (PARTITION BY NodeId ORDER BY NodeDate ASC) AS FirstDate,
         ROW_NUMBER() OVER (PARTITION BY NodeId ORDER BY NodeDate DESC) AS LastDate         
   FROM NODES
   WHERE NodeDate >= '2019/01/01' AND NodeDate < '2020/01/01'
)
UPDATE CTE SET status = 'active'
WHERE FirstDate = 1 OR LastDate = 1 
6 rows affected

SELECT * FROM NODES


NodeId
NodeDate
status


1
2019-01-01 00:00:00.0000000
active

1
2019-03-01 00:00:00.0000000
inactive

1
2019-06-01 00:00:00.0000000
inactive

1
2019-09-01 00:00:00.0000000
inactive

1
2019-12-01 00:00:00.0000000
active

2
2019-01-01 00:00:00.0000000
active

2
2019-03-01 00:00:00.0000000
inactive

2
2019-06-01 00:00:00.0000000
inactive

2
2019-09-01 00:00:00.0000000
inactive

2
2019-12-01 00:00:00.0000000
active

3
2019-01-01 00:00:00.0000000
active

3
2019-03-01 00:00:00.0000000
inactive

3
2019-06-01 00:00:00.0000000
inactive

3
2019-09-01 00:00:00.0000000
inactive

3
2019-12-01 00:00:00.0000000
active

NodeId	NodeDate	status
1	2019-01-01 00:00:00.0000000	active
1	2019-03-01 00:00:00.0000000	inactive
1	2019-06-01 00:00:00.0000000	inactive
1	2019-09-01 00:00:00.0000000	inactive
1	2019-12-01 00:00:00.0000000	active
2	2019-01-01 00:00:00.0000000	active
2	2019-03-01 00:00:00.0000000	inactive
2	2019-06-01 00:00:00.0000000	inactive
2	2019-09-01 00:00:00.0000000	inactive
2	2019-12-01 00:00:00.0000000	active
3	2019-01-01 00:00:00.0000000	active
3	2019-03-01 00:00:00.0000000	inactive
3	2019-06-01 00:00:00.0000000	inactive
3	2019-09-01 00:00:00.0000000	inactive
3	2019-12-01 00:00:00.0000000	active