Join 2 SCD tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
WITH
set_a AS (
SELECT
    '1900-01-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    1 AS value,
    56 as hidden_value
UNION ALL
SELECT
    '2020-09-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    2 AS value,
    57 as hidden_value
UNION ALL
SELECT
    '2021-01-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    2 AS value,
    58 as hidden_value
UNION ALL
SELECT
    '2015-01-01'::DATE AS _valid_from_date_time,
    'b' AS primary_key,
    10 AS value,
    58 as hidden_value
),
 
set_b AS (
SELECT
    '2000-01-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    1 AS value
UNION ALL
SELECT
    '2020-06-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    2 AS value
UNION ALL
SELECT
    '2022-01-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    3 AS value
UNION ALL
SELECT
    '1980-01-01'::DATE AS _valid_from_date_time,
    'b' AS primary_key,
    10 AS value
UNION ALL
SELECT
    '2022-01-01'::DATE AS _valid_from_date_time,
    'b' AS primary_key,
    20 AS value
),
 
set_c AS (
SELECT
    '2014-01-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    1 AS value
UNION ALL
SELECT
    '2026-01-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    2 AS value
UNION ALL
SELECT
    '2026-01-01'::DATE AS _valid_from_date_time,
    'b' AS primary_key,
    55 AS value
),
 
set_a_date AS (
SELECT
   _valid_from_date_time,
   IFNULL(LEAD(_valid_from_date_time) OVER (PARTITION BY primary_key ORDER BY _valid_from_date_time),'2999-12-31') AS _valid_to_date_time,
   primary_key,
   value,
    hidden_value
FROM
    set_a
),
 
set_b_date AS (
SELECT
   _valid_from_date_time,
   IFNULL(LEAD(_valid_from_date_time) OVER (PARTITION BY primary_key ORDER BY _valid_from_date_time),'2999-12-31') AS _valid_to_date_time,
   primary_key,
   value
FROM
    set_b
),
 
set_c_date AS (
SELECT
   _valid_from_date_time,
   IFNULL(LEAD(_valid_from_date_time) OVER (PARTITION BY primary_key ORDER BY _valid_from_date_time),'2999-12-31') AS _valid_to_date_time,
   primary_key,
   value
FROM
    set_c
),
 
key_dates AS (
  SELECT DISTINCT _valid_from_date_time, primary_key FROM set_a_date
  UNION
  SELECT DISTINCT _valid_from_date_time, primary_key FROM set_b_date
  UNION
  SELECT DISTINCT _valid_from_date_time, primary_key FROM set_c_date
)
 
 
SELECT
   key_dates._valid_from_date_time,
   LEAD(key_dates._valid_from_date_time, 1, '2999-12-31') OVER (PARTITION BY key_dates.primary_key ORDER BY key_dates._valid_from_date_time) AS _valid_to_date_time,
   set_a_date._valid_from_date_time AS a_from_date,
   set_a_date._valid_to_date_time AS a_to_date,
   set_b_date._valid_from_date_time AS b_from_date,
   set_b_date._valid_to_date_time AS b_to_date,
   set_c_date._valid_from_date_time AS c_from_date,
   set_c_date._valid_to_date_time AS c_to_date,
   set_a_date.value AS a_value,
   set_b_date.value AS b_value,
   set_c_date.value AS c_value,
   COALESCE(set_a_date.primary_key,set_b_date.primary_key,set_c_date.primary_key) AS primary_key
FROM key_dates
LEFT JOIN set_a_date
    ON key_dates._valid_from_date_time >= set_a_date._valid_from_date_time
    AND key_dates._valid_from_date_time < set_a_date._valid_to_date_time
    AND key_dates.primary_key = set_a_date.primary_key
LEFT JOIN set_b_date
    ON key_dates._valid_from_date_time >= set_b_date._valid_from_date_time
    AND key_dates._valid_from_date_time < set_b_date._valid_to_date_time
    AND key_dates.primary_key = set_b_date.primary_key
LEFT JOIN set_c_date
    ON key_dates._valid_from_date_time >= set_c_date._valid_from_date_time
    AND key_dates._valid_from_date_time < set_c_date._valid_to_date_time
    AND key_dates.primary_key = set_c_date.primary_key
ORDER BY primary_key, _valid_from_date_time