merge using max date from source

Question

Hi Expert,
Create table1
( id varchar(50), region varchar(50),subregion varchar(50), Date1 Date, locatiion varchar(50),value1 int,value2 int,value2 int,value3 int)
Insert into table1
values ('1','west','coastal','02-03-2022','Far East',22,34,44),
('1','west','coastal','02-03-2022','Far East',22,34,44),
('1','west','coastal','02-03-2022','Far East',22,34,44),
('1','west','coastal','02-03-2022','Far East',22,34,44),
('1','west','coastal','05-03-2022','Far East',22,34,44),
('2','westnew','coastalnew','02-03-2022','Far East',22,34,44),
('2','westnew','coastalnew','02-03-2022','Far East',22,34,44),
('2','westnew','coastalnew','10-03-2022','Far East',22,34,44)

Create table2
( id, region string,subregion string, Date1 Date, locatiion string,value1 int,value2 int,value2 int,value3 int)
Insert into table1

the above 2 table merged using max (date) where value1, and value2,value3 are same and group by region and subregion
i.e. expected output
('2','westnew','coastalnew','10-03-2022','Far East'22,34,44)
('1','west','coastal','05-03-2022','Far East'22,34,44)

Answer 1

How about this:

MERGE table2 T  
USING (SELECT id,region,subregion,value1,value2,value3,MAX(Date1) AS Max_Date   
       FROM table1   
       GROUP BY id,region,subregion,value1,value2,value3)S   
ON T.id=S.id AND T.region=S.region AND T.subregion=S.subregion AND T.value1=S.value1 AND T.value2=S.value2 AND T.value2=S.value2  
      
WHEN MATCHED THEN  
UPDATE SET T.Date1=S.Max_Date  
      
WHEN NOT MATCHED BY TARGET  
THEN INSERT (id,region,subregion,Date1,value1,value2,value3)   
VALUES (S.id,S.region,S.subregion,S.Max_Date,S.value1,S.value2,S.value3);  

Answer 2

Hi @Shambhu Rai
Check this:

;WITH CTE AS  
(  
SELECT *,ROW_NUMBER()OVER(PARTITION BY region,subregion,value1,value2,value3 ORDER BY Date1 DESC) AS RNum  
FROM table1  
)  
INSERT INTO table2  
SELECT  id, region,subregion, Date1, locatiion,value1,value2,value3   
FROM CTE   
WHERE RNum =1  

Best regards,
LiHong

Answer 3

Hi Expert,

I want to use the merge statement like this
MERGE table1 T
USING (SELECT DISTINCT * FROM table2)S ON T.id=S.id AND ......

WHEN MATCHED THEN
UPDATE SET ......

WHEN NOT MATCHED BY TARGET
THEN INSERT ......