SQL how to convert row with date range to many rows with date range with gaps based on a data column

Question

I want to convert data rows to date ranges in sql based on a column. Below is the sample data:

Current Data

FROMDATE    TODATE      Data
1/01/2010   31/10/2010  100
1/01/2011   31/12/2011  50
1/01/2012   31/12/2012  50
1/01/2013   31/12/2013  50
1/01/2014   31/12/2014  50
1/01/2015   12/10/2015  50
13/10/2015  31/12/2015  50
1/01/2016   21/02/2016  50
22/02/2016  31/12/2016  67
1/01/2017   2/10/2017   67
3/10/2017   31/12/2017  75
1/01/2018   31/03/2018  75
1/04/2018   30/06/2018  75
1/07/2018   31/10/2018  40
1/11/2018   31/12/2018  75
1/01/2019   31/03/2019  75
1/04/2019   31/12/2019  75
1/01/2020   1/03/2020   75

Required result is:

FROMDATE    TODATE      Data
1/01/2010   31/10/2010  100
1/01/2011   21/02/2016  50
22/02/2016  2/10/2017   67
3/10/2017   30/06/2018  75
1/07/2018   31/10/2018  40
1/11/2018   1/03/2020   75

Required List

Answer 1

I would like to give the credit to @Gordon Linoff whom one of the answer helpmed me with Gaps and islands problems which i am just sharing with you.

The reason I posted this as an answer due to the title which could be found in search results for this type of problems

I have done it using Oracle database and it should work with all standard sql database. dbfiddle for reference

SELECT t.key_id
      ,MIN(fromdate)
      ,MAX(todate)
FROM   (SELECT t.*
              ,row_number() over(ORDER BY fromdate) AS startseq
              ,row_number() over(PARTITION BY t.key_id ORDER BY fromdate) AS endseq
        FROM   some_table t) t
GROUP  BY t.key_id
         ,(startseq - endseq);