SQL Puzzle | The Merge Puzzle

In this puzzle you have to merge data into FinalReserves table. Data has been frequently coming in Reserves table. We have to read data from Reserves and merge the same into FinalReserves using 3 fields AccountNumber , TransactionDate and SalesRep. It is mandatory to use Merge statement.

Please check out the sample input values and sample expected output below.

Sample Input

Reserves

FinalReserves

Expected Output

FinalReserves

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE dbo.Reserves
(
	 AccountNumber INT ,
	 TransactionDate DATETIME,
	 SalesRep VARCHAR(50),
	 Amount NUMERIC(30,2)
)
GO

INSERT INTO dbo.Reserves VALUES
(1,GETDATE()-4,'Due',100),
(2,GETDATE()-2,'Parsons',100),
(3,GETDATE()-1,'Haverly',1000),
(4,GETDATE()-3,'Jason',100)
GO

CREATE TABLE dbo.FinalReserves
(
	 AccountNumber INT ,
	 TransactionDate DATETIME,
	 SalesRep VARCHAR(50),
	 Amount NUMERIC(30,2)
)
GO

INSERT INTO dbo.FinalReserves VALUES
(2,GETDATE()-2,'Parsons',100),
(3,GETDATE()-1,'Haverly',1000)
GO

SELECT * FROM dbo.FinalReserves
SELECT * FROM dbo.Reserves
--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--


MERGE dbo.FinalReserves AS T
USING dbo.Reserves  AS S
ON (T.AccountNumber = S.AccountNumber AND CAST(T.TransactionDate AS DATE) = CAST(S.TransactionDate AS DATE) and  T.SalesRep = S.SalesRep)
WHEN NOT MATCHED 
    THEN INSERT(AccountNumber, TransactionDate,SalesRep,Amount) 
    VALUES(S.AccountNumber, S.TransactionDate,S.SalesRep,S.Amount)
WHEN MATCHED 
    THEN UPDATE SET T.Amount = S.Amount
;
GO



--

Output

/*------------------------
MERGE dbo.FinalReserves AS T
USING dbo.Reserves  AS S
ON (T.AccountNumber = S.AccountNumber AND CAST(T.TransactionDate AS DATE) = CAST(S.TransactionDate AS DATE) and  T.SalesRep = S.SalesRep)
WHEN NOT MATCHED 
    THEN INSERT(AccountNumber, TransactionDate,SalesRep,Amount) 
    VALUES(S.AccountNumber, S.TransactionDate,S.SalesRep,S.Amount)
WHEN MATCHED 
    THEN UPDATE SET T.Amount = S.Amount
;
GO
------------------------*/

(4 row(s) affected)


/*------------------------
SELECT * FROM dbo.FinalReserves
------------------------*/
AccountNumber TransactionDate         SalesRep                                           Amount
------------- ----------------------- -------------------------------------------------- ---------------------------------------
2             2017-10-05 14:24:20.463 Parsons                                            100.00
3             2017-10-06 14:24:20.463 Haverly                                            1000.00
1             2017-10-03 14:24:20.453 Due                                                100.00
4             2017-10-04 14:24:20.453 Jason                                              100.00

(4 row(s) affected)



--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com