SQL Server 2008 (No LAG function) - Group Min/Max date when Value changes

I have an existing @table
ID  Date    Val
1   2014-10-01  1
2   2014-10-02  1
3   2014-10-03  2
4   2014-10-04  2
5   2014-10-05  2
6   2014-10-06  1
7   2014-10-07  1
8   2014-10-08  1
9   2014-10-09  1

Open in new window


The Date sequence is of importance. I need to see the first and last date for each Val sequence:
 
How do I get SQL to return the min/max dates per sequence? I need to show :
i.e.

1   2014-10-01  2014-10-02
2   2014-10-03  2014-10-05
1   2014-10-06  2014-10-09

I got this working with the help of other developers with 2012's LAG function, but I need to use 2008 please

Failed attempt:
  select t.Val,MIN(t.date),MAX(tnext.date)
from @T t join
     @T tnext
     on t.id = tnext.id - 1 and
      t.Val <> tnext.val
      group by 
      t.val

Open in new window


 
declare @T table(ID int,[Date] date,Val int)
Insert Into @T(ID,[Date],Val)
 values
(1,'2014/10/01',    1),
(2,'2014/10/02',    1),
(3,'2014/10/03',    2),
(4,'2014/10/04',    2),
(5,'2014/10/05',    2),
(6,'2014/10/06',    1),
(7,'2014/10/07',    1),
(8,'2014/10/08',    1),
(9,'2014/10/09',    1)

Open in new window

jxhardingAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should be the starter SQL:
select * 
from @t t
join @t n
  on n.[Date] = ( select max(x.[Date])
					from @t x 
						where x.[Date] >= t.[Date] 
						and x.val = t.val
						and not exists(select null from @t o
										where o.val <> t.val  
											and o.[Date] > t.[Date] 
											and o.[Date] < x.[Date] 
										)
						)
 
order by t.id

Open in new window

the next step should be easy, I wanted to post the above so you "understand" already what is going on in that query
0
 
jxhardingAuthor Commented:
Perfect thank you!!
I was able to encapsulate this into a CTE and get the results required.
It seems the saving grace here is the second @t table which has a unique ID whenever the VAL changes -which
then allows me to get the min/max date for that specific range?
0
 
PortletPaulfreelancerCommented:
Why not just use the OVER() clause with MIN() and MAX()?
select
      *
      , min([Date]) over(partition by [Val]) ValMin
      , max([Date]) over(partition by [Val]) ValMax
from @T
;

Open in new window


| ID |       Date | Val |     ValMin |     ValMax |
|----|------------|-----|------------|------------|
|  1 | 2014-10-01 |   1 | 2014-10-01 | 2014-10-09 |
|  2 | 2014-10-02 |   1 | 2014-10-01 | 2014-10-09 |
|  6 | 2014-10-06 |   1 | 2014-10-01 | 2014-10-09 |
|  7 | 2014-10-07 |   1 | 2014-10-01 | 2014-10-09 |
|  8 | 2014-10-08 |   1 | 2014-10-01 | 2014-10-09 |
|  9 | 2014-10-09 |   1 | 2014-10-01 | 2014-10-09 |
|  3 | 2014-10-03 |   2 | 2014-10-03 | 2014-10-05 |
|  4 | 2014-10-04 |   2 | 2014-10-03 | 2014-10-05 |
|  5 | 2014-10-05 |   2 | 2014-10-03 | 2014-10-05 |
        

Open in new window


{edit} so sorry, I should have refreshed the page. Seems I am not only late but well off target.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.