时间 Tel
2006-06-01 05:52:12 65460758
2006-06-11 06:09:05 13801729289
2006-06-11 06:25:13 63849310
2006-06-21 06:26:08 63849310
2006-06-22 06:34:59 54190811
2006-07-05 06:44:04 051385505580
2006-07-05 06:51:30 63849310
2006-07-06 06:53:14 13061620671
2006-07-07 06:53:22 13061620671
2006-07-08 06:58:34 54196057
2006-07-08 07:04:42 52581156求SQL
样本数据与实际星期天数不一致,只是打个比方
报表结果样本如下:所在周 范围 数据量
第01周 2006-01-03至2006-01-09 0
..
第23周 2006-06-01至2006-06-07 1
第24周 2006-06-08至2006-06-14 2
第25周 2006-06-15至2006-06-21 1
第26周 2006-06-22至2006-06-28 0
第27周 2006-06-29至2006-07-05 2
第28周 2006-07-06至2006-06-10 4
..
第53周 2006-12-21至2006-12-27 0
第54周 2006-12-28至2007-01-04 0
2006-06-01 05:52:12 65460758
2006-06-11 06:09:05 13801729289
2006-06-11 06:25:13 63849310
2006-06-21 06:26:08 63849310
2006-06-22 06:34:59 54190811
2006-07-05 06:44:04 051385505580
2006-07-05 06:51:30 63849310
2006-07-06 06:53:14 13061620671
2006-07-07 06:53:22 13061620671
2006-07-08 06:58:34 54196057
2006-07-08 07:04:42 52581156求SQL
样本数据与实际星期天数不一致,只是打个比方
报表结果样本如下:所在周 范围 数据量
第01周 2006-01-03至2006-01-09 0
..
第23周 2006-06-01至2006-06-07 1
第24周 2006-06-08至2006-06-14 2
第25周 2006-06-15至2006-06-21 1
第26周 2006-06-22至2006-06-28 0
第27周 2006-06-29至2006-07-05 2
第28周 2006-07-06至2006-06-10 4
..
第53周 2006-12-21至2006-12-27 0
第54周 2006-12-28至2007-01-04 0
2. select datepart(mm,时间),count(Tel) from TableName group by datepart(mm,时间)
create function fn_week_year(@year int)
return
@table table(weeks int,varient varchar(40))
as
begin
select top 366 id=identity(int,1,1),getdate() as datecol into # from sysobjects
update #
set datecol=dateadd(day,id,rtrim(@year-1)+'/12/31')
insert into @table
select weeks,
(
select convert(varchar,min(datecol),120)
from
(
select datepart(week,datecol) as weeks,datecol
from #
where datecol between rtrim(@year-1)+'/12/31' and rtrim(@year)+'/12/31'
)b
where a.weeks=b.weeks
)
+'至'+
(
select convert(varchar,max(datecol),120)
from
(
select datepart(week,datecol) as weeks,datecol
from #
where datecol between rtrim(@year-1)+'/12/31' and rtrim(@year)+'/12/31'
)b
where a.weeks=b.weeks
)
from
(
select datepart(week,datecol) as weeks,datecol
from #
where datecol between rtrim(@year-1)+'/12/31' and rtrim(@year)+'/12/31'
)a
group by weeks
return
endselect '第'+right(100+weeks,2)+周 as 所在周,varient as 范围 ,
count(Tel)=数据量
from fn_week_year(2006) left join tablename
on weeks=datepart(week,时间) and datepart(year,时间)=2006
group by weeks,varient
returns
@table table(weeks int,varient varchar(40))
as
function 里面好像不能访问临时表,这个function 你测试过吗?
(
@st datetime,
@et datetime
)
AS
BEGIN
declare @t table (id int)
insert into @t
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 select identity(int, 1,1) id
into #dt
from @t a, @t b, @t c, @t d
/*
declare @st datetime, @et datetime
set @st = '2006-05-01'
set @et = '2006-06-01'
*/ select dateadd(day,id-1, @st) as odate, datepart(yyyy, dateadd(day,id-1, @st)) as oyear, datepart(ww, dateadd(day,id-1, @st)) as oweek
from #dt
where id<=datediff(day, @st, @et) + 1 drop table #dt
ENd
GO
-------------------------------------------
--O,sorry
--沒看清,try againselect '第'+convert(varchar,datepart(wk,时间))+'周' as 所在周
,convert(varchar,min(时间),120)+'至'+convert(varchar,max(时间),120) as 范围
,count(Tel) as 数据量
from TableName group by datepart(wk,时间)--月份一樣,把datepart(wk,时间)-->datepart(mm,时间)
这不是我要的效果
2006-01-01 00:00:00.000 2006 1
2006-01-02 00:00:00.000 2006 1
2006-01-03 00:00:00.000 2006 1
2006-01-04 00:00:00.000 2006 1
2006-01-05 00:00:00.000 2006 1
2006-01-06 00:00:00.000 2006 1
2006-01-07 00:00:00.000 2006 1
2006-01-08 00:00:00.000 2006 2
2006-01-09 00:00:00.000 2006 2
2006-01-10 00:00:00.000 2006 2
2006-01-11 00:00:00.000 2006 2
2006-01-12 00:00:00.000 2006 2
select @year=2006select top 366 id=identity(int,1,1),getdate() as datecol into # from sysobjects
update #
set datecol=dateadd(day,id,rtrim(@year-1)+'/12/31')
select weeks,
(
select convert(varchar,min(datecol),120)
from
(
select datepart(week,datecol) as weeks,datecol
from #
where datecol between rtrim(@year-1)+'/12/31' and rtrim(@year)+'/12/31'
)b
where a.weeks=b.weeks
)
+'至'+
(
select convert(varchar,max(datecol),120)
from
(
select datepart(week,datecol) as weeks,datecol
from #
where datecol between rtrim(@year-1)+'/12/31' and rtrim(@year)+'/12/31'
)b
where a.weeks=b.weeks
)as varient
into #1
from
(
select datepart(week,datecol) as weeks,datecol
from #
where datecol between rtrim(@year-1)+'/12/31' and rtrim(@year)+'/12/31'
)a
group by weeksselect '第'+right(100+weeks,2)+周 as 所在周,varient as 范围 ,
count(Tel)=数据量
from #1 left join tablename
on weeks=datepart(week,时间) and datepart(year,时间)=@year
group by weeks,varient
的数据有错误:select '第'+convert(varchar,datepart(wk,time_in))+'周' as 所在周
,convert(varchar,min(time_in),120)+'至'+convert(varchar,max(time_in),120) as 范围
,count(ucid) as 数据量
from CallDetail_Temp group by datepart(wk,time_in)服务器: 消息 8120,级别 16,状态 1,行 1
列 'CallDetail_Temp.TIME_IN' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
语法有错误服务器: 消息 170,级别 15,状态 1,行 42
第 42 行: '=' 附近有语法错误。
select '第'+right(100+weeks,2)+周 as 所在周,varient as 范围 ,
count(Tel) as 数据量
from #1 left join tablename
on weeks=datepart(week,时间) and datepart(year,时间)=@year
group by weeks,varient--机器重装就是麻烦
新的错误服务器: 消息 8624,级别 16,状态 21,行 9
内部 SQL Server 错误。
的数据有错误:select '第'+convert(varchar,datepart(wk,time_in))+'周' as 所在周
,convert(varchar,min(time_in),120)+'至'+convert(varchar,max(time_in),120) as 范围
,count(ucid) as 数据量
from CallDetail_Temp group by datepart(wk,time_in)服务器: 消息 8120,级别 16,状态 1,行 1
列 'CallDetail_Temp.TIME_IN' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。------------------------------------------
--暈~~~這個....group by 自己改下就可以了吧select '第'+convert(varchar,datepart(wk,时间))+'周' as 所在周
,convert(varchar,min(时间),120)+'至'+convert(varchar,max(时间),120) as 范围
,count(Tel) as 数据量
from TableName
group by '第'+convert(varchar,datepart(wk,时间))+'周'
你可以先建一個周表A,插入53條周紀錄(第1周,第2周...)
然後A left join (統計語句) B on A.所在周=B.所在周
问题是如何确定第一周,因为第一周有可能是从1月2日或者5日开始,如何生成这个年的星期表
也就是說,每年的第一周,天數<=7天
你可以試下select datepart(wk,'2006-01-01') 結果1
select datepart(wk,'2006-01-07') 結果1
因為2006-01-01正好是星期天
但是2007-01-01是星期一,所以2007年的第一周為6天
select datepart(wk,'2007-01-01') 結果1
select datepart(wk,'2007-01-07') 結果2所以,你只要讓SQL自動去匹配好了.
用convert转再除7+1-开始日期就可以获得所有带周no表,
如
week_no ,tel,
1 333
1 222
3 444
再group week求count,范围最好再用week反向计算出来,,,,这样比较容易一些,,也不出什么问题
select datediff(****)具体我这里没有办法查测试(@网吧),,所以可以自己写了