title: How To Update Multiple Conditions Using MERGE Statements
body: <p>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 </p>
  
  <pre><code>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 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
  
    WHEN NOT MATCHED BY TARGET THEN
    INSERT(
    ID,
    StartDate,
    ENDDate,
    Designation
    )
    VALUES
    (s.ID,
    s.StartDate,
    s.EndDate,
    s.Designation)
    ;
  </code></pre>
  
  <p>Now, this code doesn't 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 &lt; 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. </p>
  
  <pre><code>Source-Table1
  ID       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
  </code></pre>
  
  <p>Now I want my Target Table to have </p>
  
  <pre><code>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
  </code></pre>
  
  <p>I am not able to do this Using MERGE statements.Can anyone of you help me to get this result?</p>