-1

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

大陸北方網友
  • 3,696
  • 3
  • 12
  • 37
Ruhi
  • 3
  • 2
  • 4
    Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Aug 19 '20 at 06:07

1 Answers1

0

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);
Sujitmohanty30
  • 3,256
  • 2
  • 5
  • 23