add batch
remove batch
split batch
comment selection
show hidden batches
hide batch
highlight batch
db<>fiddle
Db2
Firebird
MariaDB
MySQL
Node.js
Oracle
Postgres
SQLite
SQL Server
TimescaleDB
YugabyteDB
Developer-C 11.1
3.0
4.0
10.2
10.3
10.4
10.5
10.6
10.7
10.8
10.9
5.5
5.6
5.7
8.0
18
11g Release 2
18c
21c
23c
8.4
9.3
9.4
9.5
9.6
10
11
12
13
14
15
16
3.8
3.16
3.27
3.39
2012
2014
2016
2017
2017 (Linux)
2019
2019 (Linux)
2022
2.11
2.14
2.6
2.8
2.18
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
Sakila
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
run
markdown
donate
feedback
about
By using db<>fiddle, you agree to license everything you submit by
Creative Commons CC0
.
create table fileandunit( FILE_, UNIT, DATE_, ID_SEQUENCE ) as select 10, 34, to_date( '02-JAN-2000', 'DD-MON-YYYY'), 10 from dual union all select 10, 34, to_date( '05-JAN-2000', 'DD-MON-YYYY'), 11 from dual union all select 10, 40, to_date( '05-JAN-2000', 'DD-MON-YYYY'), 12 from dual union all select 10, 40, to_date( '02-JAN-2000', 'DD-MON-YYYY'), 13 from dual union all select 10, 40, to_date( '02-JAN-2000', 'DD-MON-YYYY'), 14 from dual union all select 10, 40, to_date( '15-JAN-2000', 'DD-MON-YYYY'), 15 from dual union all select 10, 34, to_date( '16-JAN-2000', 'DD-MON-YYYY'), 16 from dual union all select 10, 70, to_date( '17-JAN-2000', 'DD-MON-YYYY'), 17 from dual union all select 10, 70, to_date( '28-JAN-2000', 'DD-MON-YYYY'), 18 from dual ;
9 rows affected
select coalesce( RP.m_file, RP.s_file ) file_ , coalesce( RP.m_unit, RP.s_unit ) unit_ , coalesce( ( RP.m_end - RP.m_start ), 1 ) days_ from fileandunit match_recognize( partition by file_ order by id_sequence measures enddt.file_ as m_file -- m_: for multiple days , enddt.unit as m_unit , startdt.date_ as m_start , enddt.date_ as m_end , singledt.file_ as s_file -- s_: single day , singledt.unit as s_unit , singledt.date_ as s_date one row per match pattern ( ( startdt iffydt* enddt ) | singledt ) -- multiple days (or) single day define startdt as ( prev( date_ ) <= date_ or prev( date_ ) is null ) and ( prev( unit ) <> unit or prev( unit ) is null ) -- , enddt as ( next( date_ ) >= date_ or next( date_ ) is null ) and ( next( unit ) <> unit or next( unit ) is null ) -- , iffydt as ( prev( date_ ) >= date_ ) -- detect incorrect dates inside a multiple day block and ( prev( unit ) = unit ) -- , singledt as ( prev( date_ ) = date_ - 1 and next( date_ ) = date_ + 1 ) and ( prev( unit ) <> unit and next( unit ) <> unit ) ) RP ;
FILE_
UNIT_
DAYS_
10
34
3
10
40
10
10
34
1
10
70
11