I wanna get query result Group By and Orderd by Date

Question

Can I get result like below with Oracle Query? Group by Type but seperated by date order. Date can't be overlaped.

[RAW DATA]

TYPE    FR_DATE             TO_DATE             INCOME
A       04/14 01:04:15      04/14   22:10:15    100 
A       04/15 03:02:22      04/16   18:10:44    200
B       04/17 05:13:22      04/17   20:11:44    300
B       04/18 01:24:22      04/20   23:13:44    100
A       04/22 05:11:22      04/22   19:25:44    400
A       04/23 02:12:22      04/24   17:43:44    200
B       04/25 02:19:22      04/28   18:32:44    500

[RESULT]

TYPE    FR_DATE             TO_DATE             INCOME_SUM
A       04/14 01:04:15      04/16 18:10:44      300 
B       04/17 05:13:22      04/20 23:13:44      400 
A       04/22 05:11:22      04/24 17:43:44      600
B       04/25 02:19:22      04/28 18:32:44      500 

Answer 1

Another gaps-and-islands approach is to use the match_recognize clause, available since 12c, to perform pattern matching:

select type,
  min(fr_date) as fr_date,
  max(to_date) as to_date,
  sum(income) as income_sum
from your_table
match_recognize (
  order by fr_date
  measures match_number() as mnum
  all rows per match
  pattern (start_type same_type*)
  define same_type as (type = prev(type))
)
group by type, mnum
order by fr_date

TYPE	FR_DATE	TO_DATE	INCOME_SUM
A	2023-04-14 01:04:15	2023-04-16 18:10:44	300
B	2023-04-17 05:13:22	2023-04-20 23:13:44	400
A	2023-04-22 05:11:22	2023-04-24 17:43:44	600
B	2023-04-25 02:19:22	2023-04-28 18:32:44	500

fiddle