3

I am trying to solve a problem of how to find the maximum count of consecutive years in a series of records. In the following example:

ID  Year
1 1993
1 1994
1 1995
1 1995
1 2001
1 2002
2 1993
2 1995
2 1996
2 1996
2 1998
2 1999
2 2000
2 2001
2 2001

My result set should look like 

id   count
1      3
2      4

I have to write the code in oracle SQL.

avg998877
  • 127
  • 1
  • 4
  • 12
  • Please Use SQL Fiddle. – Vivek Sadh May 31 '13 at 17:21
  • This sounds like a job for a recursive CTE, where each iteration matches a row with the row for the prior year. Not sure how that looks in Oracle, though, so I'll just leave it as a comment that might help someone else with an answer. – Joel Coehoorn May 31 '13 at 17:26

3 Answers3

5

This will produce your desired result:

select
  id,
  ayear,
  byear,
  yeardiff
from
(
  select
    a.id,
    a.year ayear,
    b.year byear,
    (b.year - a.year)+1 yeardiff,
    dense_rank() over (partition by a.id order by (b.year - a.year) desc) rank
  from
    years a
    join years b on a.id = b.id 
        and b.year > a.year
  where
    b.year - a.year = 
      (select count(*)-1
         from years a1
        where a.id = a1.id
             and a1.year between a.year and b.year)
)
where
  rank = 1

EDIT updated to display start/end years of longest stretch.

SQLFiddle

Joe
  • 6,767
  • 1
  • 16
  • 29
  • This is amazing response..though I still have to understand the code(am new to sql)..But i really really appreciate you for your time on this. Thanks a lot JOE. – avg998877 May 31 '13 at 18:11
  • Hi Joe, I have duplicate rows in my actual data set. And this code doesn't seem to work in that case. Could you please help me with a modified version of your code that can also address duplicate row..(Note: I modified the test data in the main question) – avg998877 Jun 07 '13 at 18:23
  • Sure, just change: "(select count(*)-1" to "(select count(distinct year)-1" – Joe Jun 07 '13 at 18:32
  • No problem. I updated my original answer above to reflect the change. – Joe Jun 07 '13 at 19:08
  • Hi Joe, is there a way I can also display the years used to calculate the year diff in this query? Thanks in advance. -AVG – avg998877 Nov 11 '13 at 19:11
  • Do you mean you want to see all of the years that make up the longest stretch? – Joe Nov 11 '13 at 20:07
  • not all years but just the start and end of that stretch. Thanks – avg998877 Nov 12 '13 at 17:16
4

Try:

with cte as
(select t.id, t.year, d.d, row_number() over (partition by t.id, d.d 
                                              order by t.year) rn
 from (select -1 d from dual union all select 1 d from dual) d
 cross join my_table t 
 where not exists
       (select null
        from my_table o
        where t.id = o.id and t.year = o.year-d.d) )
select s.id, max(e.year-s.year)+1 year_count
from cte s
join cte e on s.id = e.id and s.rn = e.rn and e.d=1
where s.d=-1
group by s.id

SQLFiddle here.

  • Hi, I am looking for a solution with out CTE..most likely with the use of flags. This is because I am trying to build a SAP web intelligence report using this SQL..and solution with flags would be very helpful for me – avg998877 May 31 '13 at 17:51
  • But I really appreciate your quick response MARK. – avg998877 May 31 '13 at 18:02
  • @user2283660: Why would the use of a SAP web intelligence report preclude CTEs? What do you mean by "flags"? –  May 31 '13 at 18:10
  • I came across a solution..where flags are used to achieve the desired result..but unfortunately it was written in SQL server and is not good for oracle. Source: http://www.sqlservercentral.com/Forums/Topic10547-8-1.aspx?Update=1 . Sorry If i've confused you. Thanks – avg998877 May 31 '13 at 18:18
  • @user2283660: The `d` column in the CTE acts in a similar way to the `flag` column in the linked example. You still haven't explained why you can't use a CTE in a SAP web intelligence report. –  May 31 '13 at 18:25
  • To be frank I am not sure if I can use CTE in the custom SQL of SAP web intelligence tool(am new to both SQL and Web Intelligence). So am looking for a traditional approach for this. – avg998877 May 31 '13 at 18:28
  • Please enlighten me if my assumptions are wrong. Am open to suggestions. Thanks MARK. – avg998877 May 31 '13 at 18:30
  • @user2283660: I don't know - I haven't used the SAP web intelligence tool, so I don't know what restrictions it has. From what you were saying, it sounded as though you were certain that it couldn't use CTEs; I wondered why this would be the case. –  May 31 '13 at 18:34
  • But I did search for its use in the tool in the major SAP forums..I couldn't find any, so i just assumed that i can't use it in the tool. Can you please suggest(any good books, forums) me some ways to improve my sql skills. thanks in advance – avg998877 May 31 '13 at 18:41
-2

with t1 as ( select id, year, lead(year) over(partition by id order by year) as nxt_yr from tree ), t2 as ( select id, year, nxt_yr, nxt_yr - year as diff from t1 ), t3 as (select id, year, nxt_yr, diff, lag(diff) over(partition by id order by year) as diff_lag, lead(diff) over(partition by id order by year) as diff_lead from t2 ) select id, sum(diff) + 1 from t3 where ((diff = 1) and (diff_lag = 1)) or ((diff = 1) and (diff_lead = 1)) group by id

weirdbeard
  • 1
  • Welcome to Stack Overflow. You may want to use better formatting for your answers and explain why this answers the given question. – Yun Aug 16 '21 at 18:18