100分在求一SQL句
有一表如下 取的日期范06-12-15到07-01-14

日期 姓名 得分
2006-12-15 A 10
. A 20
. A
. A
2007-01-14 A
2006-12-15 B
. B
. B
2007-01-14 B
. C
. C
. C
. C
. D
. .
求一句或的方法得到下面的果每周的得分
姓名 始日期(周日) 束日期(周尾六) 一周得分和
A 2006-12-15 2006-12-16
A 2006-12-17 2006-12-23
..
B 2006-12-15 2006-12-16
..
..








[解决办法]
select 姓名,cast(datepart(year,dateadd(day,-1,日期)) as varchar(4))+cast(datepart(week,dateadd(day,-1,日期)) as varchar(2)) as 年周,
sum(得分) as 得分 from tb
group by cast(datepart(year,dateadd(day,-1,日期)) as varchar(4))+cast(datepart(week,dateadd(day,-1,日期)) as varchar(2)),姓名

----
不好意思,写得太快了,再扩展一下就好了
[解决办法]
create table tb
(
t_date datetime,
t_week int
)
declare @s_date datetime,@e_date as datetime,@t_date as datetime,@i int,@j int
set @s_date = cast( '20061215 ' as datetime)
set @t_date = cast( '20061215 ' as datetime)
set @e_date = cast( '20070114 ' as datetime)
set @i = 1
set @j = 0
while (@t_date <=@e_date )

begin
SELECT @j=DATEPART(dw,@t_date)
if @j = 7
set @i = @i + 1
insert into tb values (@t_date,@i)
SELECT @t_date =dateadd(day,1,@t_date)
end
select 姓名,sum(分) from 表,tb where 日期 = t_date group by 姓名,t_week

[解决办法]
A2006-12-10~2006-12-1630.0
A2006-12-17~2006-12-2370.0
A2007-01-14~2007-01-2050.0
B2006-12-10~2006-12-16130.0
B2006-12-17~2006-12-2380.0
B2007-01-14~2007-01-2090.0
C2007-01-14~2007-01-20106.0
D2007-01-14~2007-01-204.0

[解决办法]
--拆分日期

create table T([Date] datetime)
insert T select '2006-12-15 '
union all select '2006-12-16 '
union all select '2006-12-17 '
union all select '2006-12-18 '
union all select '2006-12-19 '
union all select '2006-12-20 '
union all select '2006-12-21 '
union all select '2006-12-22 '
union all select '2006-12-23 '
union all select '2006-12-24 '
union all select '2006-12-25 '
union all select '2006-12-26 '
union all select '2006-12-27 '
union all select '2006-12-28 '
union all select '2006-12-29 '
union all select '2006-12-30 '
union all select '2006-12-31 '
union all select '2007-01-01 '
union all select '2007-01-02 '
union all select '2007-01-03 '
union all select '2007-01-04 '
union all select '2007-01-05 '
union all select '2007-01-06 '
union all select '2007-01-07 '
union all select '2007-01-08 '
union all select '2007-01-09 '
union all select '2007-01-10 '
union all select '2007-01-11 '
union all select '2007-01-12 '
union all select '2007-01-13 '
union all select '2007-01-14 '

select distinct
始日期=case when (datepart(weekday, [Date])+@@datefirst-1)%7=0 then [Date]
when [Date]-6 between (select min([date]) from T) and (select max([date]) from T) then [Date]-6
else (select min([date]) from T) end,
束日期=case when (datepart(weekday, [Date])+@@datefirst-1)%7=6 then [Date]
when [Date]+6 between (select min([date]) from T) and (select max([date]) from T) then [Date]+6
else (select max([date]) from T) end
from T
where (datepart(weekday, [Date])+@@datefirst-1)%7 in (0, 6)

--result

始日期 束日期
------------------------------------------------------ ------------------------------------------------------
2006-12-15 00:00:00.000 2006-12-16 00:00:00.000
2006-12-17 00:00:00.000 2006-12-23 00:00:00.000
2006-12-24 00:00:00.000 2006-12-30 00:00:00.000
2006-12-31 00:00:00.000 2007-01-06 00:00:00.000
2007-01-07 00:00:00.000 2007-01-13 00:00:00.000
2007-01-14 00:00:00.000 2007-01-14 00:00:00.000

(6 row(s) affected)