Merging with one of the multiple matching records in Target Table using merge update

316 Views Asked by At

I have two Tables with almost identical columns. The requirement is to merge the Rows of source Table into Target table. The Problem is, the Target table has more than one matching rows with differne type of Expenses and I want to merge the Source record with any one of the matching rows.

Here are my tables

    CREATE TABLE Target
    (
      [rowId] int,
      [EmployeeNumber] int, 
      [ServiceFromDate] date, 
      [ServiceToDate] date, 
      [BillType] varchar(3), 
      [BillDate] date,
      [Expense] int, 
      [Trace] varchar(max)
)

INSERT INTO Target ([rowId], [EmployeeNumber], [ServiceFromDate], [ServiceToDate], [BillType], [BillDate], Expense,[Trace])
VALUES
    (1, 101, '2020-01-01', '2020-01-01', '111', '2020-01-01',100,'Trace-1-101-111'),
    (2, 101, '2020-01-01', '2020-01-01', '112', '2020-01-01',100,'Trace-2-101-112'),
    (3, 101, '2020-01-01', '2020-01-01', '111', '2020-01-01',100,'Trace-3-101-111'),
    (4, 101, '2020-01-01', '2020-01-01', '121', '2020-01-01',100,'Trace-4-101-121'),
    (5, 101, '2020-01-01', '2020-01-01', '121', '2020-01-01',100,'Trace-5-101-121'),
    (6, 101, '2020-01-01', '2020-01-01', '121', '2020-01-01',100,'Trace-6-101-121'),
    (7, 102, '2020-01-01', '2020-01-01', '131', '2020-01-01',100,'Trace-7-101-131'),
    (8, 102, '2020-01-01', '2020-01-01', '114', '2020-01-01',100,'Trace-8-101-114'),
    (9, 102, '2020-01-01', '2020-01-01', '114', '2020-01-01',100,'Trace-9-101-114'),
    (10, 102, '2020-01-01', '2020-01-01', '112', '2020-01-01',100,'Trace-10-101-112');



CREATE TABLE Source1
    (
      [EmployeeNumber] int, 
      [ServiceFromDate] date, 
      [ServiceToDate] date, 
      [BillType] varchar(3), 
      [BillDate] date,
      [Expense] int, 
      [Trace] varchar(max)
)


go

INSERT INTO Source1 ([EmployeeNumber], [ServiceFromDate], [ServiceToDate], [BillType], [BillDate], [Expense],[Trace])
VALUES
    (101, '2020-01-01', '2020-01-01', '115', '2020-01-01',100,'Trace-1-101-115'),
    (101, '2020-01-01', '2020-01-01', '115', '2020-01-01',100,'Trace-2-101-115'),
    (101, '2020-01-01', '2020-01-01', '115', '2020-01-01',100,'Trace-3-101-115'),
    (101, '2020-02-01', '2020-01-01', '125', '2020-01-01',100,'Trace-4-101-125'),
    (101, '2020-01-01', '2020-01-01', '125', '2020-01-01',100,'Trace-5-101-125'),
    (101, '2020-03-01', '2020-01-01', '125', '2020-01-01',100,'Trace-6-101-125'),
    (102, '2020-01-01', '2020-01-01', '135', '2020-01-01',100,'Trace-7-101-135'),
    (105, '2020-01-01', '2020-01-01', '115', '2020-01-01',100,'Trace-8-101-115'),
    (102, '2020-01-01', '2020-01-01', '115', '2020-01-01',100,'Trace-9-101-115'),
    (102, '2020-01-01', '2020-01-01', '115', '2020-01-01',100,'Trace-10-101-115');

I want to merge the bill types ending with '%5' with any one of the target table records where the EmployeNumber, Service Dates and Billtypes are matching.

Any help will be appreciated.

The columns to match for Merging are [Employeenumber] [BillDate],

If these Matches, then the Expenses are to be added and Traces are to be appended.

The Expected output will look like this

(1, 101, '2020-01-01', '2020-01-01', '111', '2020-01-01',400,'Trace-1-101-111, Trace-1-101-115, Trace-2-101-115, Trace-3-101-115...'),
(2, 101, '2020-01-01', '2020-01-01', '112', '2020-01-01',100,'Trace-2-101-112'),
(3, 101, '2020-01-01', '2020-01-01', '111', '2020-01-01',100,'Trace-3-101-111'),
(4, 101, '2020-01-01', '2020-01-01', '121', '2020-01-01',100,'Trace-4-101-121'),
(5, 101, '2020-01-01', '2020-01-01', '121', '2020-01-01',100,'Trace-5-101-121'),
(6, 101, '2020-01-01', '2020-01-01', '121', '2020-01-01',100,'Trace-6-101-121'),

(7, 102, '2020-01-01', '2020-01-01', '131', '2020-01-01',400,'Trace-7-101-131,Trace-7-101-135,Trace-9-101-115,Trace-10-101-115'),
(8, 102, '2020-01-01', '2020-01-01', '114', '2020-01-01',100,'Trace-8-101-114'),
(9, 102, '2020-01-01', '2020-01-01', '114', '2020-01-01',100,'Trace-9-101-114'),
(10, 102, '2020-01-01', '2020-01-01', '112', '2020-01-01',100,'Trace-10-101-112');

(105, '2020-01-01', '2020-01-01', '115', '2020-01-01',100,'Trace-8-101-115'), // New Row as no matching record
(101, '2020-01-01', '2020-01-01', '115', '2020-01-01',100,'Trace-3-101-115'), // New Row as no matching record
0

There are 0 best solutions below