求连续播放时间

/*
表的结构如下:
CREATE TABLE [dbo].[剧目表](
[autoID] [int] IDENTITY(1,1) NOT NULL,
[地区] [tinyint] NOT NULL,
[频道] [smallint] NOT NULL,
[日期] [smalldatetime] NOT NULL,
[名称] [varchar](40) NOT NULL,
[开始时间] [tinyint] NOT NULL,
        [播出次数] [smallint] NOT NULL......

要求首先按照地区，频道，名称，日期排序，如果地区相同，频道相同，名称相同，那么日期差在十天之内的，播出次数相同，大于等于10天，则在原有播出次数的值上加一。结果形式大概如下:
地区 频道 名称 日期 播出次数
21 14 张三 2008-10-1 1
21 14 张三 2008-10-12 2
21 14 张三 2008-10-13 2
谢谢各位
*/

declare @jumubiao table (diqu int,pingdao int,mingcheng varchar(50),riqi datetime,bofangcishu int)
declare @temp table (diqu int,pingdao int,mingcheng varchar(50),riqi datetime,bofangcishu int,rn int)
declare @temp1 table (diqu int,pingdao int,mingcheng varchar(50),riqi datetime,bofangcishu int,rn int)
insert @jumubiao values (21,14 ,'tom', '2008-10-1',1 )
insert @jumubiao values (21,14 ,'tom', '2008-10-25',1 )
insert @jumubiao values (21,14 ,'tom', '2008-10-12',1 )
insert @jumubiao values (21,14 ,'tom', '2008-10-13',1 )
insert @jumubiao values (21,14 ,'tom', '2008-10-24',1 )
insert @jumubiao values (21,14 ,'tom', '2008-11-24',1 )

insert @jumubiao values (21,14 ,'zhansan', '2008-10-1',1 )
insert @jumubiao values (21,14 ,'zhansan', '2008-10-10',1 )
insert @jumubiao values (21,14 ,'zhansan', '2008-10-11',1 )
insert @jumubiao values (21,14 ,'zhansan', '2008-10-2',1 )
insert @jumubiao values (21,14 ,'zhansan', '2008-10-3',1 )
insert @jumubiao values (21,14 ,'zhansan', '2008-10-25',1 )
insert @jumubiao values (21,14 ,'zhansan', '2008-10-12',1 )
insert @jumubiao values (21,14 ,'zhansan', '2008-10-13',1 )
insert @jumubiao values (21,14 ,'zhansan', '2008-10-24',1 )
insert @jumubiao values (21,14 ,'zhansan', '2008-11-24',1 )
insert @jumubiao values (21,14 ,'zhansan', '2008-12-27',1 )

insert into @temp
select *,rn = row_number() over (partition by diqu,pingdao,mingcheng order by riqi) from @jumubiao

--答案1
select t1.* ,isnull((select case when datediff(day,t1.riqi,t2.riqi) > 10 then t1.bofangcishu + 1 else t1.bofangcishu end   from @temp t2 where t1.diqu = t2.diqu and t1.pingdao = t2.pingdao and
t1.mingcheng = t2.mingcheng
and t1.rn = t2.rn - 1 ),t1.bofangcishu) as bofangcishu
from @temp t1
--答案2
select t1.* ,isnull((select case when datediff(day,t2.riqi,t1.riqi) > 10 then t1.bofangcishu + 1 else t1.bofangcishu end   from @temp t2 where t1.diqu = t2.diqu and t1.pingdao = t2.pingdao and
t1.mingcheng = t2.mingcheng
and t1.rn -1 = t2.rn  ),t1.bofangcishu) as bofangcishu
from @temp t1
--答案3
select t1.diqu,t1.pingdao,t1.mingcheng,t1.riqi,
case when datediff(day,t2.riqi,t1.riqi) > 10 then t1.bofangcishu + 1 else t1.bofangcishu end as bofangcishu
,t1.rn
from @temp t1 left join @temp t2 on
t1.rn -1 = t2.rn and
t1.diqu = t2.diqu and t1.pingdao = t2.pingdao and
t1.mingcheng = t2.mingcheng

--扩展 求连续播放时间 假定10天内为连续播放
insert into @temp1
select t1.diqu,t1.pingdao,t1.mingcheng,t1.riqi,
case when datediff(day,t2.riqi,t1.riqi) > 10 then t1.bofangcishu + 1 else t1.bofangcishu end as bofangcishu
,t1.rn
from @temp t1 left join @temp t2 on
t1.rn -1 = t2.rn and
t1.diqu = t2.diqu and t1.pingdao = t2.pingdao and
t1.mingcheng = t2.mingcheng

--答案

select diqu,pingdao,mingcheng,sum(datediff(day,t2riqi,t1riqi))
from
( 
select  t1.diqu,t1.pingdao,t1.mingcheng,t1.riqi t1riqi,t2.riqi t2riqi from @temp1 t1 left join @temp1 t2 on
t1.rn -1 = t2.rn and
t1.diqu = t2.diqu and t1.pingdao = t2.pingdao and
t1.mingcheng = t2.mingcheng
where t1.bofangcishu = 1
) a
group by diqu,pingdao,mingcheng