SQL Server query to consolidate dates

I am trying to create a key based on an id plus a code plus a start date which spans a range of dates and consolidates id + code until another id + code comes along. Here is the data:

ID      CODE        EFFECTIVE     TERM
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

The result I am trying to get is:

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

SQL Server 2开发者_开发知识库005.

Any help is much appreciated, under the gun as usual and brain dead on this one. Thanks.

---

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

Test here: https://data.stackexchange.com/stackoverflow/q/113660/