October 28, 2024 at 2:51 pm
I have a table like below
ID Ident source val opendate closedate IsActive
--------------------------------------------------------
101 euid01 File01 x010 01-01-2023 01-01-2999 1
101 euid02 File01 x111 01-01-2023 01-01-2999 1
101 euid02 File01 x222 01-01-2023 01-10-2024 0
102 euid01 File11 x010 01-01-2023 01-01-2999 1
102 euid02 File12 x333 01-01-2023 01-01-2999 1
102 euid02 File10 x444 01-01-2023 01-10-2024 0
I want to merge it as below using a function or procedure or a block
by passing two values eg. 101,102
If the ident and val are same for two different IDs ignore that rec during merge, closedate can be get sysdate or getdate in case of terming them out.
All the records in 102 will be termed out except which has Ident and val same, in this case we ignore that record. Also find the create table script in code.
ID Ident source val opendate closedate IsActive
-----------------------------------------------------------
101 euid01 File01 x010 01-01-2023 01-01-2999 1
101 euid02 File01 x111 01-01-2023 01-01-2999 1
101 euid02 File01 x222 01-01-2023 01-10-2024 0
101 euid02 File12 x333 01-01-2023 22-10-2024 0
101 euid02 File10 x444 01-01-2023 22-10-2024 0
CREATE TABLE MDM (
ID INT,
Ident VARCHAR(20),
source VARCHAR(20),
val VARCHAR(20),
opendate datetime,
closedate datetime,
Isactive bit)
insert into MDM (ID,Ident,source ,val,opendate,closedate,Isactive)
VALUES
(101, 'euid01' ,'File01', 'x010', '01-01-2023', '01-01-2999' ,1),
(101, 'euid02' ,'File01', 'x111', '01-01-2023', '01-01-2999' ,1),
(102, 'euid02' ,'File01', 'x222', '01-01-2023', '01-10-2024' ,0),
(102, 'euid01' ,'File01', 'x010', '01-01-2023', '01-01-2999' ,1),
(102, 'euid02' ,'File02', 'x333', '01-01-2023', '01-01-2999' ,1),
(102, 'euid02' ,'File10', 'x444', '01-01-2023', '01-10-2024' ,0)
October 28, 2024 at 3:10 pm
What does 'termed out' mean?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 28, 2024 at 3:19 pm
Termed out means making isActive =0, which is no longer active, I am trying something like below, which not yet complete, requires cleaning and fixes
PROCEDURE merge_by_id (@id_from INT, @id_to INT) as
begin
declare @vFrom INT = @id_from
declare @vInto INT = @id_to
;With cte AS
(SELECT ROW_NUMBER() OVER (ORDER BY ID,identifier, source, val, opendate) AS rowid,
ID,identifier, source, val, opendate,closedate,isActive FROM MDM)
--SELECT * FROM CTE;
MERGE INTO CTE t
USING (SELECT ROW_NUMBER() OVER (ORDER BY ID,identifier, source, val, opendate) AS rw, identifier, source, val, opendate,
CASE WHEN (identifier,val) IN (SELECT identifier,val FROM MDM WHERE id = @vInto) THEN 1 END AS delete_flag
FROM MDM WHERE id = @vFrom) s
ON (t.rowid = s.rw)
WHEN MATCHED
THEN
UPDATE SET id = @vInto, closedate = getdate(), isActive = 0
DELETE WHERE delete_flag = 1;
END
October 28, 2024 at 3:51 pm
Is this an accurate description? you want to update all rows where ID = 102 (set id = 101, Isactive = 0, closedate = getdate()) unless the combination of ident and val already exists with id = 101, in which case you want to delete the row?
Does this do what you want? Note that the third row in the insert statement is for id 102, but the image shows 101. I know it's not a merge, but I'm trying to get the rules correct first.
DECLARE @RetainID INT =101,
@MergeID INT = 102
DELETE a
--SELECT *
FROM MDM AS a
WHERE a.ID = @MergeID
AND EXISTS (SELECT 1 FROM mdm
WHERE ID = @RetainID
AND Ident = a.Ident
AND val = a.Val)
UPDATE a
SET a.ID = @RetainID,
a.Isactive = 0,
a.Closedate = GETDATE()
FROM mdm AS a
WHERE a.ID = @MergeID
-- Not necessary after the delete
AND NOT EXISTS (SELECT 1 FROM mdm
WHERE ID = @RetainID
AND Ident = a.Ident
AND val = a.Val)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply