SQL Server Query for Date Consolidation

Question

I am trying to create a key based on id plus code plus start date, which covers a date range and consolidates id + code until another id + code appears. Here are the data:

ID CODE EFFECTIVE TERM
9950 H0504402 01/07/2007 08/31/2007
9950 H0504404 01/09/2007 01/31/2008
9950 H0504402 02/01/2008 01/21/2009
9950 H0504402 01/03/2009 01/21/2010
9950 H0504404 01/02/2010 11/02/2011
9950 H0504404 01/03/2011 NULL

The result that I am trying to get is:

KEY EFFECTIVE TERM
9950_H0504402_20070701 01/07/2007 08/31/2007
9950_H0504404_20070901 01/09/2007 01/31/2008
9950_H0504402_20080201 02/01/2008 01/21/2010
9950_H0504404_20100201 01/02/2010 NULL

SQL Server 2005.

, , . .

Answer 1

declare @t table(id int, code char(8), effective datetime, term datetime)
insert @t values
(9950,    'H0504402',    '07/01/2007',    '08/31/2007'),
(9950    ,'H0504404'    ,'09/01/2007',    '01/31/2008'),
(9950    ,'H0504402'    ,'02/01/2008',    '01/21/2009'),
(9950    ,'H0504402'    ,'03/01/2009',    '01/21/2010'),
(9950    ,'H0504404'    ,'02/01/2010',    '02/11/2011'),
(9950    ,'H0504404'    ,'03/01/2011',    NULL)

;with cte as
(
-- add rownumber (rn)
select id, code, effective, term, row_number() over (order by effective) rn
from @t
), 
b as
(
-- add group (r)
select *, 1 r  from cte where rn = 1
union all
select cte.* , case when b.id <> cte.id or b.code <> cte.code 
then r + 1 else r end
from cte join b on b.rn + 1 = cte.rn
), 
c as
(
-- find last and first row
select id, code, min(effective) over (partition by r) effective, 
term, row_number() over (partition by r order by rn desc) nrn
,rn, r
from b
)
-- convert columns to look like description
select cast(id as varchar(9))+ code + '_' + convert(char(8), effective,112) [KEY], 
effective, term from c where nrn = 1 order by rn 
option (maxrecursion 0)-- added to prevent problems in a production environment

: http://data.stackexchange.com/stackoverflow/q/113660/