CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Onur Cete
Onur Cete

Reputation: 273

Count Max Sequence row

I have a table with dates information (ex: 201501,201502,201510 etc.) Row is varchar but refer yyymm format dates.

For example;


dates
---
201501
201502
201503
201505
201506
201507
201508
201509
201510
201512
201601
201602

there are sequence months but '201504' and '201511' are missing.

I want to count rownum until each missing date. ex: until '201504' there are 3 row, after '201504' until next missing date ('201511') there are 6 row. Lastly, 2 row.

I want max sequence. for this example output is 6. Thank you for your helps.

Upvotes: 0

Views: 193

Answers (2)

MT0
MT0

Reputation: 168051

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( dates ) AS
          SELECT 201501 FROM DUAL
UNION ALL SELECT 201502 FROM DUAL
UNION ALL SELECT 201503 FROM DUAL
UNION ALL SELECT 201505 FROM DUAL
UNION ALL SELECT 201506 FROM DUAL
UNION ALL SELECT 201507 FROM DUAL
UNION ALL SELECT 201508 FROM DUAL
UNION ALL SELECT 201509 FROM DUAL
UNION ALL SELECT 201510 FROM DUAL
UNION ALL SELECT 201512 FROM DUAL
UNION ALL SELECT 201601 FROM DUAL
UNION ALL SELECT 201602 FROM DUAL

Query 1:

SELECT MAX( diff ) AS result
FROM (
  SELECT CASE WHEN end_grp IS NOT NULL THEN 1
              ELSE LEAD( end_grp ) IGNORE NULLS OVER ( ORDER BY DATES ) - dates + 1 END as diff
  FROM (
    SELECT DATES,
           CASE next_date WHEN dates+1 THEN NULL ELSE dates END AS end_grp
    FROM (
      SELECT DATES,
             LEAD( DATES ) OVER ( ORDER BY DATES ) AS next_date
      FROM   TABLE_NAME
    )
  )
)

Results:

| RESULT |
|--------|
|      6 |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269993

You can do this with a difference of a row numbers from a months count to assign a group id to the sequences. So, the following identifies such sequences:

select min(d), max(d), count(*)
from (select t.*,
             (to_number(substr(d, 1, 4)) * 12 + to_number(substr(d, 5, 2)) -
              row_number() over (order by d)
             ) as grp
      from t
     ) t
group by grp;

You can get the max length as:

with cte as (
      select min(d), max(d), count(*) as len
      from (select t.*,
                   (to_number(substr(d, 1, 4)) * 12 + to_number(substr(d, 5, 2)) - 
                    row_number() over (order by d)
                   ) as grp
            from t
           ) t
      group by grp
     )
select max(len)
from cte;

Upvotes: 1

Related Questions