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:
- 2.3.22 until 5.3.22: name = Daniel
- 5.3.22 until 2.4.22: name = Maria
- 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?