SQL Server 2022

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

CREATE TABLE [dbo].[srcTable](
    [Id] [INT] NULL,
    [Value] [INT] NULL,
    [QueryDate] [DATE] NULL
) 

CREATE TABLE [dbo].[destTable](
    [Id] [INT] NULL,
    [Value] [INT] NULL,
    [QueryDate] [DATE] NULL
) 

INSERT INTO [dbo].[srcTable]
(
    [Id],
    [Value],
    [QueryDate]
)
SELECT 2, 3, '2023-01-03'
UNION ALL
SELECT 2, 3, '2023-03-03'
UNION ALL
SELECT 3, 4, '2023-03-02'
UNION ALL
SELECT 5, 6, '2023-03-04'
UNION ALL
SELECT 3, 4, '2023-03-17'

INSERT INTO [dbo].[destTable]
(
    [Id],
    [Value],
    [QueryDate]
)
SELECT 1, 2, '2023-03-03'
UNION ALL
SELECT 1, 2, '2023-03-10'

7 rows affected

WITH cte AS  (
    SELECT *
    FROM (
      SELECT *,
        ROW_NUMBER() OVER (PARTITION BY DATETRUNC(week, S.QueryDate) ORDER BY S.QueryDate DESC) AS rn
      FROM dbo.srcTable AS S
    ) S
    WHERE S.rn = 1
) 
MERGE dbo.destTable AS T
USING cte AS S
   ON DATETRUNC(week, S.QueryDate) = DATETRUNC(week, T.QueryDate)
WHEN MATCHED AND S.QueryDate >= T.QueryDate THEN
  UPDATE SET
    Id = S.Id,
    Value = S.Value,
    QueryDate = S.QueryDate
WHEN NOT MATCHED THEN 
  INSERT (Id, Value, QueryDate) 
  VALUES (S.Id, S.Value, S.QueryDate)
OUTPUT inserted.*, deleted.*, $Action;


Id
Value
QueryDate
Id
Value
QueryDate
$Action


2
3
2023-01-03
null
null
null
INSERT

3
4
2023-03-17
null
null
null
INSERT

5
6
2023-03-04
1
2
2023-03-03
UPDATE


SELECT *
FROM desttable


Id
Value
QueryDate


5
6
2023-03-04

1
2
2023-03-10

2
3
2023-01-03

3
4
2023-03-17

Id	Value	QueryDate	Id	Value	QueryDate	$Action
2	3	2023-01-03	null	null	null	INSERT
3	4	2023-03-17	null	null	null	INSERT
5	6	2023-03-04	1	2	2023-03-03	UPDATE