CODEWITHSUNDEEP
sqloracle-databasedate
Mr.P
Mr.P

Reputation: 1257

how to extract min and max date in consecutive line - oracle

How can I get min() and max() date from the table below? 

id | sub_cat_id |   date
----------------------------
 2 |     1      | 1/2/2015
 2 |     1      | 2/2/2015
 2 |     1      | 3/2/2015
 2 |     1      | 6/2/2015
 2 |     1      | 7/2/2015
 2 |     3      | 1/2/2015
 2 |     3      | 30/3/2015
 2 |     3      | 31/3/2015
 2 |     3      | 1/4/2015
 2 |     3      | 2/4/2015

I would like to get result -> 

id | sub_cat_id | start_date | end_date
----------------------------------------
 2 |     1      |  1/2/2015  | 3/2/2015
 2 |     1      |  6/2/2015  | 7/2/2015
 2 |     3      |  1/2/2015  | 1/2/2015
 2 |     3      |  30/3/2015 | 2/4/2015

Could you please advice me how to do this?

Thank you

Upvotes: 1

Views: 751

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

You could do it using ROW_NUMBER analytic function. See Find range of consecutive values in a sequence of numbers or dates.

For example, let's say I have range of numbers with missing gaps:

SQL> WITH DATA(dates) AS(
  2  SELECT  DATE '2015-01-01' FROM dual UNION
  3  SELECT  DATE '2015-01-02' FROM dual UNION
  4  SELECT  DATE '2015-01-03' FROM dual UNION
  5  SELECT  DATE '2015-01-05' FROM dual UNION
  6  SELECT  DATE '2015-01-06' FROM dual UNION
  7  SELECT  DATE '2015-01-07' FROM dual UNION
  8  SELECT  DATE '2015-01-10' FROM dual UNION
  9  SELECT  DATE '2015-01-11' FROM dual UNION
 10  SELECT  DATE '2015-01-12' FROM dual UNION
 11  SELECT  DATE '2015-01-13' FROM dual UNION
 12  SELECT  DATE '2015-01-20' FROM dual
 13  )
 14  SELECT MIN(dates),
 15         MAX(dates)
 16  FROM
 17    ( SELECT dates, dates - row_number() OVER(ORDER BY dates) rn
 18      FROM DATA
 19    )
 20  GROUP BY rn
 21  ORDER BY rn;

MIN(DATES MAX(DATES
--------- ---------
01-JAN-15 03-JAN-15
05-JAN-15 07-JAN-15
10-JAN-15 13-JAN-15
20-JAN-15 20-JAN-15

SQL>

Upvotes: 4

Related Questions