Coherent intervals in SQL?

Question

Consider a simple Diary table:

 Date   Mood
 ----   ----
 1      Good
 2      Good
 3      Good
 4      Bad
 5      Bad
 6      Good

I am interested in the mood intervals, so that I would get a result like:

 Mood   BeginDate   EndDate
 ----   ---------   -------
 Good   1           3
 Bad    4           5 
 Good   6           6

Is that possible in SQL without using iteration?

Answer 1

If you subtracts a sequence number from the days that starts for each mood, you will get a constant when the values are adjacent:

 Date   Mood    Seqnum
 1      Good       1
 2      Good       2
 3      Good       3
 4      Bad        1
 5      Bad        2
 6      Good       4

Then you can use aggregation to do what you want. If date is really a number:

select mood, min(date), max(date)
from (select t.*,
             row_number() over (partition by mood order by date) as seqnum
      from t
     ) t
group by mood, (date - seqnum);

If date is really a date:

select mood, min(date), max(date)
from (select t.*,
             row_number() over (partition by mood order by date) as seqnum
      from t
     ) t
group by mood, dateadd(day, - seqnum, date)