March 26, 2018 at 1:04 pm
I have to compare source and Target data and MERGE them accordingly. But I have multiple conditions.
Below is my first set of source data
Source-Table1
ID StartDate EndDate Designation
1 2018-01-01 2199-12-31 Associate Engineer
2 2018-02-01 2199-12-31 Software Engineer
Target-Table2
ID StartDate EndDate Designation
1 2018-01-01 2199-12-31 Associate Engineer
2 2018-02-01 2199-12-31 Software Engineer
Create table Table1(
Id int,
StartDate datetime,
EndDate Datetime,
Designation nvarchar(100)
)
Create table Table2(
Id int,
StartDate datetime,
EndDate Datetime,
Designation nvarchar(100)
)--insert values
insert into Table1 values(1,'2018-01-01','2199-12-31','Associate Engineer')
insert into Table1 values(2,'2018-02-01','2199-12-31','Software Engineer')
--Use Merge to Update/Insert valuesMERGE Table2 t
USING Table1 s
on t.id=s.id
WHEN MATCHED THEN
update SET
t.id=s.id,
t.StartDate=s.StartDate,
t.EndDate=s.EndDate,
t.Designation=s.Designation
WHEN NOT MATCHED BY TARGET THEN
INSERT(
ID,
StartDate,
ENDDate,
Designation
)
VALUES
(s.ID,
s.StartDate,
s.EndDate,
s.Designation)
;
Now, this code doesnt work if I have to update multiple conditions ,for Example,if My source data has below values,then each ID cannot have overlap values.
When there is new record for each ID, I have to check on StartDate, If start Date is < enddate then, update my old endDate to last day of the previous month and
insert the new record else if ID and StartDate match, then update all records. If ID and StartDate does not mach then Insert.
Source-Table1ID StartDate EndDate Designation
1 2018-01-01 2199-12-31 Associate Engineer
1 2018-02-01 2199-12-31 Software Engineer
2 2018-02-01 2199-12-31 Software Engineer
2 2018-03-01 2199-12-31 Senior Software Engineer
Now I want my Target Table to have ID StartDate EndDate Designation
1 2018-01-01 2018-01-31 Associate Engineer
1 2018-02-01 2199-12-31 Software Engineer
2 2018-02-01 2018-02-28 Software Engineer
2 2018-03-01 2199-12-31 Senior Software Engineer
I am not able to do this Using MERGE statements.Can anyone of you help me to get this result?
April 6, 2018 at 11:31 pm
Hi,
Looks like you are working with slowly changing dimensions. An alternate way is to split the MERGE statement into two, INSERT and UPDATE separately.
--Use Merge to Update/Insert values
MERGE Table2 t
USING Table1 s
ON t.id=s.id
WHEN MATCHED THEN
UPDATE
SET
t.id=s.id,
t.StartDate=s.StartDate,
t.EndDate=s.EndDate,
t.Designation=s.Designation;
GO
MERGE Table2 t
USING Table1 s
ON t.id=s.id and t.startdate <> s.startdate
WHEN NOT MATCHED BY TARGET THEN
INSERT(
ID,
StartDate,
ENDDate,
Designation
)
VALUES
(s.ID,
s.StartDate,
s.EndDate,
s.Designation) ;
GO
To expire the previous record, you can use the following SELECT query and convert it as a separate UPDATE statement.
SELECT T.ID, T.STARTDATE, (CASE WHEN T.STARTDATE < X.MaxDate THEN x.MaxDate-1 ELSE T.ENDDATE END) AS ENDDATE, T.DESIGNATION
FROM Table1 t
left join (
select Top 1 ID, MAX(t1.startdate) MaxDate
from Table1 t1
Group by t1.ID
Order by 1 desc
)X
on t.ID + 1 = x.ID
=======================================================================
April 7, 2018 at 10:58 am
I think you would only need to match by ID from the target table. I assume that your target table would be having id as a unique field?. (Edit: i think you have id and startdate as a unique key)
So the tricky part would be to write a query based on how you want the output rows should look like in the "USING" portion of the MERGE. After that what you need to do would be to match it on the ON clause using the ID field and the Start date.
Eg:
MERGE
INTO Table2 t
USING (select x.id
,x.startdate
,case when dateadd(dd
,-1
,lead(x.startdate) over(partition by x.id order by x.startdate asc)
) is null then
/* Incase the lead of startdate is a null implies its the current open record with date (2199-12-31)*/
x.enddate
else dateadd(dd
,-1
,lead(x.startdate) over(partition by x.id order by x.startdate asc)
)
end
as enddate
,x.designation
from Table1 x
)s
ON t.id=s.id
AND t.startdate=s.startdate
WHEN MATCHED THEN
UPDATE
SET t.EndDate=s.EndDate,
t.Designation=s.Designation
WHEN NOT MATCHED THEN
INSERT (id
,startdate
,enddate
,designation)
VALUES(s.id
,s.startdate
,s.enddate
,s.designation
)
GO
April 7, 2018 at 1:23 pm
Modify the ON to
On t.id = s.id
And t.startdate = s.startdate
then add a check for
When not matched by source
Then
Update
Set enddate = eomonth(loaddate)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 8, 2018 at 7:36 pm
Why don't you use case statement and subqueries in the set part instead?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply