How to update table with different validity-overlaps of staging- and result-table?

Viewed 28

I have a dimension table like this

ID__DIM | employeeNumber | name          | validFrom  | validUntil
-------------------------
 7      | 4211           | Daniel        | 02.03.2022 | 11.04.2022

This means that the name employee with employeeNumber = 4211 from 2.3.22 to 11.4.22 is Daniel.

Case 1: The staging table is

employeeNumber | name   | validFrom  | validUntil
-------------------------
 4211          | Maria| 05.03.2022 | 02.04.2022

So for some reason the name of the same employee from 5.3.22 to 2.4.22 is (for some reason) Maria. This means in total that when we update we have three different cases:

  1. 2.3.22 until 5.3.22: name = Daniel
  2. 5.3.22 until 2.4.22: name = Maria
  3. 2.4.22 until 11.4.22: name = Daniel

This corresponds to the following updated dimension table

ID__DIM | employeeNumber | name          | validFrom  | validUntil
-------------------------
 7      | 4211           | Daniel        | 02.03.2022 | 05.03.2022
 7      | 4211           | Maria         | 05.03.2022 | 02.04.2022
 7      | 4211           | Daniel        | 02.04.2022 | 11.04.2022

Case 2: If the staging table was

employeeNumber | name   | validFrom  | validUntil
-------------------------
 4211          | Maria| 05.02.2022 | 09.04.2022

The expected result for the dimension table is

ID__DIM | employeeNumber | name          | validFrom  | validUntil
-------------------------
 7      | 4211           | Maria         | 05.02.2022 | 09.04.2022
 7      | 4211           | Daniel        | 09.04.2022 | 11.04.2022

I have to use microsoft sql server and my approach is to run multiple merge statements but this becomes very complicated. Is this possible to achieve in one merge statement for all possible cases? What is the general approach?

0 Answers
Related