Hello everyone
I hope someone can help me with my problem, as I cant seem to find a solution.
Created some sample data to better visualize my problem.
Table 1 shows the validity HUB_TEMP_HKEY
| HUB_TEMP_HKEY | VALID_FROM | VALID_TO |
|---|---|---|
| aaaa | 2023-01-02 | 2023-01-20 |
| aaaa | 2023-01-27 | NULL |
| cccc | 2023-01-02 | 2023-01-20 |
| cccc | 2023-01-24 | 2023-01-25 |
| cccc | 2023-01-27 | NULL |
| dddd | 2023-01-02 | 2023-01-23 |
| dddd | 2023-01-26 | NULL |
Table 2 is the change history of a HUB_TEMP_KEY. For each change of attributes, a new line is created with a HASH_DIFF as a key and its validity. The most current record has valid_to NUL.
| HUB_TEMP_HKEY | HASH_DIFF | CHANGEVALUE | VALID_FROM | VALID_TO |
|---|---|---|---|---|
| aaaa | aaaa1 | a1 | 2023-01-02 | 2023-01-12 |
| aaaa | aaaa2 | a2 | 2023-01-12 | 2023-01-27 |
| aaaa | aaaa3 | a3 | 2023-01-27 | NULL |
| cccc | cccc1 | c1 | 2023-01-02 | 2023-01-20 |
| cccc | cccc2 | c2 | 2023-01-20 | 2023-01-24 |
| cccc | cccc3 | c3 | 2023-01-24 | NULL |
| dddd | dddd1 | d1 | 2023-01-02 | 2023-01-19 |
| dddd | dddd2 | d2 | 2023-01-19 | 2023-01-30 |
| dddd | dddd3 | d3 | 2023-01-30 | NULL |
Now to my poblem. I want to generate the table you see below.
For each record in Table 2, I need to check if the HUB_TEMP_HKEY was valid.
If there is a gap in Table 1 during that period, the record of Table 2 should set valid_to as the start of the gap and add a duplicated record of Table 2 with the valid_from when the gap ended. But only if the gap ended inbetween the valid_from and valid_to of the record in table 2.
It is unfortunately super complex to describe, I am sorry.
I really hope this makes sense to someone.
Table 3:
| HUB_TEMP_HKEY | HASH_DIFF | CHANGEVALUE | VALID_FROM | VALID_TO |
|---|---|---|---|---|
| aaaa | aaaa1 | a1 | 2023-01-02 | 2023-01-12 |
| aaaa | aaaa2 | a2 | 2023-01-12 | 2023-01-20 |
| aaaa | aaaa3 | a3 | 2023-01-27 | NULL |
| cccc | cccc1 | c1 | 2023-01-02 | 2023-01-20 |
| cccc | cccc2 | c2 | 2023-01-24 | 2023-01-24 |
| cccc | cccc3 | c3 | 2023-01-24 | 2023-01-25 |
| cccc | cccc3 | c3 | 2023-01-27 | NULL |
| dddd | dddd1 | d1 | 2023-01-02 | 2023-01-19 |
| dddd | dddd2 | d2 | 2023-01-19 | 2023-01-23 |
| dddd | dddd2 | d2 | 2023-01-26 | 2023-01-30 |
| dddd | dddd3 | d3 | 2023-01-30 | NULL |
Any help would be really appreciated and thank you in advance.
Luca