统计代码算法查错，只要是在一年的最后一周统计就出错，其他任何时间段都没问题

[glboy]

测试代码：
declare @tb table
(
Client_1RecType varchar(10),
Client_1RecTime varchar(10)
)
insert @tb
select '来访','2005-08-11' union all
select '来访','2005-08-12' union all
select '来访','2005-08-15' union all
select '来访','2005-08-15' union all
select '来电','2005-08-16' union all
select '来访','2005-08-17' union all
select '来访','2005-08-17' union all
select '来电','2005-08-17' union all
select '来电','2005-08-17' union all
select '来电','2005-08-17' union all
select '来访','2005-08-18' union all
select '来访','2005-08-18' union all
select '来访','2005-08-18' union all
select '来电','2005-08-18' union all
select '来访','2005-08-18' union all
select '来访','2005-08-18' union all
select '来电','2005-08-18' union all
select '来电','2005-08-19' union all
select '来电','2005-08-28' union all
select '来电','2005-08-28' union all
select '来电','2005-08-29' union all
select '来电','2005-08-29' union all
select '来电','2005-08-29' union all
select '来电','2005-08-29'

declare @startDate datetime,@endDate datetime
select @startDate='2005-08-11',
@endDate='2005-12-25'
--重点注意：只要@endDate设置为某年最后一周的某个日期就会出问题

declare @weeks table([周数] int)
declare @tmp datetime
set @tmp=@startDate
while datepart(week,@tmp)<=datepart(week,@endDate)
begin
insert @weeks select datepart(week,@tmp)
set @tmp=dateadd(day,7,@tmp)
end

--查询
select [总数量]=isnull(T.[总数量],0),
[周数]= '第'+convert(varchar,W.[周数]-datepart(week,@startDate)+1)+'周'
from @weeks W
left join
(
select
[总数量]=count(1),
[周数]=datepart(week,Client_1RecTime)
from @tb
where Client_1RecTime between @startDate and @endDate
group by datepart(week,Client_1RecTime)
)T on W.[周数]=T.[周数]

[vivianfdlpw]

declare @tb table
(
Client_1RecType varchar(10),
Client_1RecTime varchar(10)
)
insert @tb
select '来访','2005-08-11' union all
select '来访','2005-08-12' union all
select '来访','2005-08-15' union all
select '来访','2005-08-15' union all
select '来电','2005-08-16' union all
select '来访','2005-08-17' union all
select '来访','2005-08-17' union all
select '来电','2005-08-17' union all
select '来电','2005-08-17' union all
select '来电','2005-08-17' union all
select '来访','2005-08-18' union all
select '来访','2005-08-18' union all
select '来访','2005-08-18' union all
select '来电','2005-08-18' union all
select '来访','2005-08-18' union all
select '来访','2005-08-18' union all
select '来电','2005-08-18' union all
select '来电','2005-08-19' union all
select '来电','2005-08-28' union all
select '来电','2005-08-28' union all
select '来电','2005-08-29' union all
select '来电','2005-08-29' union all
select '来电','2005-08-29' union all
select '来电','2005-08-29'

declare @startDate datetime,@endDate datetime
select @startDate='2005-08-11',
@endDate='2006-1-7'

declare @weeks table([周数] int)
declare @tmp datetime,@i int
select @tmp=@startDate,@i=1
insert @weeks select datepart(week,@startDate)
while dateadd(day,7,@tmp)<=@endDate
begin
insert @weeks select datepart(week,@startDate)+@i
select @tmp=dateadd(day,7,@tmp),@i=@i+1
end

--查询
select [总数量]=isnull(T.[总数量],0),
[周数]= '第'+convert(varchar,W.[周数]-datepart(week,@startDate)+1)+'周'
from @weeks W
left join
(
select
[总数量]=count(1),
[周数]=datepart(week,Client_1RecTime)
from @tb
where Client_1RecTime between @startDate and @endDate
group by datepart(week,Client_1RecTime)
)T on W.[周数]=T.[周数]


-- 结果
/*
总数量 周数
----------- ----------------------------------
2 第1周
16 第2周
0 第3周
6 第4周
0 第5周
0 第6周
0 第7周
0 第8周
0 第9周
0 第10周
0 第11周
0 第12周
0 第13周
0 第14周
0 第15周
0 第16周
0 第17周
0 第18周
0 第19周
0 第20周
0 第21周
0 第22周

(22 row(s) affected)
*/

[liuyxit]

--试试这个(是统计第几周前的总次数吧):
declare @tb table
(
Client_1RecType varchar(10),
Client_1RecTime varchar(10)
)
insert @tb
select '来访','2005-01-01' union all
select '来访','2005-01-02' union all
select '来访','2005-01-08' union all
select '来访','2005-08-11' union all
select '来访','2005-08-12' union all
select '来访','2005-08-15' union all
select '来访','2005-08-15' union all
select '来电','2005-08-16' union all
select '来访','2005-08-17' union all
select '来访','2005-08-17' union all
select '来电','2005-08-17' union all
select '来电','2005-08-17' union all
select '来电','2005-08-17' union all
select '来访','2005-08-18' union all
select '来访','2005-08-18' union all
select '来访','2005-08-18' union all
select '来电','2005-08-18' union all
select '来访','2005-08-18' union all
select '来访','2005-08-18' union all
select '来电','2005-08-18' union all
select '来电','2005-08-19' union all
select '来电','2005-08-28' union all
select '来电','2005-08-28' union all
select '来电','2005-08-29' union all
select '来电','2005-08-29' union all
select '来电','2005-08-29' union all
select '来电','2005-08-29'

--select datepart(week,Client_1RecTime),* from @tb

declare @startDate datetime,@endDate datetime
select @startDate='2005-01-01',
@endDate='2005-12-25'

select
[周数]= '第'+ ltrim(str(min(datepart(week,a.Client_1RecTime)))) +'周',
[总数量]=(select count(Client_1RecType) from @tb b where datepart(week,b.Client_1RecTime) <= datepart(week,a.Client_1RecTime))
from @tb a
where a.Client_1RecTime between @startDate and @endDate
group by datepart(week,a.Client_1RecTime)

/*结果
周数 总数量
-------------- -----------
第1周 1
第2周 3
第33周 5
第34周 21
第36周 27
*/

--我奇怪的是2005-01-01是第二周？2004-01-01是第一周？晕。。。

[liuyxit]

--我奇怪的是'2005-01-02'是第二周？'2004-01-02'是第一周？晕。。。

[liuyxit]

哦，意思可能我理解错了
你是要从开始日期开始的周数