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 No comments
{count} votes

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