Share via

merge using max date from source

Shambhu Rai 1,411 Reputation points
2022-09-01T08:53:07.357+00:00

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)

Azure SQL Database
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
3,065 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,705 questions
Developer technologies | Transact-SQL
SQL Server | Other
0 comments
Accepted answer
  1. LiHongMSFT-4306 31,571 Reputation points
    2022-09-01T09:39:46.787+00:00

    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);

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. LiHongMSFT-4306 31,571 Reputation points
    2022-09-01T09:07:53.527+00:00

    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

    1 person found this answer helpful.
    0 comments
  2. Shambhu Rai 1,411 Reputation points
    2022-09-01T09:24:09.817+00:00

    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 ......

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.