SQL Server 查询以合并日期

Question

我正在尝试创建一个基于 id 加上代码加上开始日期的 key ，该日期跨越一系列日期并合并 id + 代码，直到另一个 id + 代码出现。数据如下:

ID      CODE        EFFECTIVE     TERM9950    H0504402    07/01/2007    08/31/20079950    H0504404    09/01/2007    01/31/20089950    H0504402    02/01/2008    01/21/20099950    H0504402    03/01/2009    01/21/20109950    H0504404    02/01/2010    02/11/20119950    H0504404    03/01/2011    NULL

我想要得到的结果是:

KEY                       EFFECTIVE     TERM9950_H0504402_20070701    07/01/2007    08/31/20079950_H0504404_20070901    09/01/2007    01/31/20089950_H0504402_20080201    02/01/2008    01/21/20109950_H0504404_20100201    02/01/2010    NULL

SQL Server 2005。

非常感谢任何帮助，像往常一样在 Guzzle 下，在这件事上脑死亡。谢谢。

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

在这里测试: https://data.stackexchange.com/stackoverflow/q/113660/