相连日期组合为一条记录，不连的日期单独一条
请假信息：
---表结构 tb
ID badge name date
110066张立英2011-08-01
210066张立英2011-08-02
310066张立英2011-08-03
410066张立英2011-08-04
510066张立英2011-08-05
610066张立英2011-08-06
710066张立英2011-08-07
810066张立英2011-08-08
910066张立英2011-08-09
1010066张立英2011-08-10
1110070戴开忠2011-08-07
1210070戴开忠2011-08-13
1310070戴开忠2011-08-14
1410070戴开忠2011-08-15
1510070戴开忠2011-08-16
1610070戴开忠2011-08-17
1710070戴开忠2011-08-18
1810075成定才2011-08-06
2210109吴伦秀2011-08-01
2410109吴伦秀2011-08-13
2510109吴伦秀2011-08-14
2610109吴伦秀2011-08-16


想要结果：

badge name begindate enddate
10066张立英 2011-08-01 2011-08-10
10070戴开忠 2011-08-07 2011-08-07
10070戴开忠 2011-08-13 2011-08-18
10075成定才 2011-08-06 2011-08-06
10109吴伦秀 2011-08-01 2011-08-01
10109吴伦秀 2011-08-13 2011-08-14
10109吴伦秀 2011-08-16 2011-08-16

在线等各位大侠

[解决办法]

SQL code

declare @tb table ([ID] int,[badge] int,[name] varchar(6),[date] datetime)insert @tbselect 1,10066,'张立英','2011-08-01' union allselect 2,10066,'张立英','2011-08-02' union allselect 3,10066,'张立英','2011-08-03' union allselect 4,10066,'张立英','2011-08-04' union allselect 5,10066,'张立英','2011-08-05' union allselect 6,10066,'张立英','2011-08-06' union allselect 7,10066,'张立英','2011-08-07' union allselect 8,10066,'张立英','2011-08-08' union allselect 9,10066,'张立英','2011-08-09' union allselect 10,10066,'张立英','2011-08-10' union allselect 11,10070,'戴开忠','2011-08-07' union allselect 12,10070,'戴开忠','2011-08-13' union allselect 13,10070,'戴开忠','2011-08-14' union allselect 14,10070,'戴开忠','2011-08-15' union allselect 15,10070,'戴开忠','2011-08-16' union allselect 16,10070,'戴开忠','2011-08-17' union allselect 17,10070,'戴开忠','2011-08-18' union allselect 18,10075,'成定才','2011-08-06' union allselect 22,10109,'吴伦秀','2011-08-01' union allselect 24,10109,'吴伦秀','2011-08-13' union allselect 25,10109,'吴伦秀','2011-08-14' union allselect 26,10109,'吴伦秀','2011-08-16'select a.*,b.date  from (select row_number() over(order by t.badge)as rn, t.badge,t.name,date from @tb twhere not exists (select 1 from @tb where badge=t.badge and datediff(dd,t.date,date)=-1))as a join (select row_number() over(order by t.badge)as rn, badge,date from @tb twhere not exists (select 1 from @tb where badge=t.badge and datediff(dd,t.date,date)=1))as bon a.badge=b.badge and a.rn =b.rn /*rn    badge    name    date    date1    10066    张立英    2011-08-01 00:00:00.000    2011-08-10 00:00:00.0002    10070    戴开忠    2011-08-07 00:00:00.000    2011-08-07 00:00:00.0003    10070    戴开忠    2011-08-13 00:00:00.000    2011-08-18 00:00:00.0004    10075    成定才    2011-08-06 00:00:00.000    2011-08-06 00:00:00.0005    10109    吴伦秀    2011-08-01 00:00:00.000    2011-08-01 00:00:00.0006    10109    吴伦秀    2011-08-13 00:00:00.000    2011-08-14 00:00:00.0007    10109    吴伦秀    2011-08-16 00:00:00.000    2011-08-16 00:00:00.000*/