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
|
$min_date = ($a, $b) -> (IF($a IS NULL, $b, IF($b IS NULL, $a, IF($a < $b, $a, $b))));
$max_date = ($a, $b) -> (IF($a IS NULL OR $b IS NULL, NULL, IF($a > $b, $a, $b)));
$overlaps = ($a, $b) -> (
IF(
$a.date_from IS NOT NULL AND $b.date_to IS NOT NULL AND $a.date_from >= $b.date_to, FALSE,
IF(
$b.date_from IS NOT NULL AND $a.date_to IS NOT NULL AND $b.date_from >= $a.date_to, FALSE,
TRUE
)
)
);
$f = ($key, $left, $right) -> (
ListNotNull(
ListFlatten(
ListMap(
$left, ($x) -> (
ListMap(
$right, ($y) -> (
IF($overlaps($x, $y), <|key: $key, value1: $x.value, value2: $y.value, date_from: $max_date($x.date_from, $y.date_from), date_to: $min_date($x.date_to, $y.date_to)|>, NULL)
)
)
)
)
)
)
);
$t1 = [
<|date_from: "2026-01-01", date_to: "2026-01-10", id: 1, value: "a1"|>,
<|date_from: "2026-01-10", date_to: "2026-01-25", id: 1, value: "a2"|>,
<|date_from: "2026-01-25", date_to: NULL, id: 1, value: "a3"|>,
];
$t2 = [
<|date_from: "2026-01-01", date_to: "2026-01-20", id: 1, value: "b1"|>,
<|date_from: "2026-01-20", date_to: "2026-01-23", id: 1, value: "b2"|>,
<|date_from: "2026-01-23", date_to: NULL, id: 1, value: "b3"|>,
];
COMBINE AS_TABLE($t1) AS t1
PRESORT id, date_from, date_to
WITH AS_TABLE($t2) as t2
PRESORT id, date_from, date_to
ON t1.id = t2.id
USING $f(TableRow(), TableRow());
|
