Report Contiguous Dates Problem

1
2
3
4
5
6
7
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| fail_date    | date    |
+--------------+---------+
Primary key for this table is fail_date.
Failed table contains the days of failed tasks.

1
2
3
4
5
6
7
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| success_date | date    |
+--------------+---------+
Primary key for this table is success_date.
Succeeded table contains the days of succeeded tasks.

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
Failed table:
+-------------------+
| fail_date         |
+-------------------+
| 2018-12-28        |
| 2018-12-29        |
| 2019-01-04        |
| 2019-01-05        |
+-------------------+

Succeeded table:
+-------------------+
| success_date      |
+-------------------+
| 2018-12-30        |
| 2018-12-31        |
| 2019-01-01        |
| 2019-01-02        |
| 2019-01-03        |
| 2019-01-06        |
+-------------------+


Result table:
+--------------+--------------+--------------+
| period_state | start_date   | end_date     |
+--------------+--------------+--------------+
| succeeded    | 2019-01-01   | 2019-01-03   |
| failed       | 2019-01-04   | 2019-01-05   |
| succeeded    | 2019-01-06   | 2019-01-06   |
+--------------+--------------+--------------+

The report ignored the system state in 2018 as we care about the system in the period 2019-01-01 to 2019-12-31.
From 2019-01-01 to 2019-01-03 all tasks succeeded and the system state was "succeeded".
From 2019-01-04 to 2019-01-05 all tasks failed and system state was "failed".
From 2019-01-06 to 2019-01-06 all tasks succeeded and system state was "succeeded".

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select 'succeeded' as period_state, min(success_date) as start_date, 
    max(success_date) as end_date 
from (select success_date, row_number() over(order by success_date) as row_num
    from Succeeded
    where success_date between '2019-01-01' and '2019-12-31') t
group by dayofyear(success_date) - row_num

union

select 'failed' as period_state, min(fail_date) as start_date, 
    max(fail_date) as end_date 
from (select fail_date, row_number() over(order by fail_date) as row_num
    from Failed
    where fail_date between '2019-01-01' and '2019-12-31') t
group by dayofyear(fail_date) - row_num

order by start_date