帮看看下面的存储过程,我是想统计一个月的医生工作量,但有个要求是这样的,每天的都是昨天3点到今天3点算一个工作日,每月的20号,为结帐日期 ,21号为下个月的,帮我看看我这段那里出问题了
--创建存储过程
/*
write by cyg
date 2006-01-23
统计月医生工作量*/
CREATE procedure sp_getInfo(@month datetime,@time as int=0) --@time,统计时间中小时的限定值
as
begin
--输入参数合法性判定
if @time<0 or @time>23
return
declare @s varchar(8000),@day int
set @s='select 医生名称'
--提取被统计时间范围内的数据,并插入临时表#t 21 00:00:00为21号00点00分00秒而 20 23:59:59为下月20号的开始
select * into #t from yphjxshz where dateadd(hh,24-@time,发生日期) between convert(char(8),dateadd(mm,-1,@month),120)+'21 00:00:00' and convert(char(8),@month,120)+'20 23:59:59'
--定义游标,获取当前统计范围内不重复的日期数据信息
declare t_cursor cursor for select distinct day(发生日期) from yphjxshz order by day(发生日期)
--打开游标
open t_cursor
fetch next from t_cursor into @day
--遍历游标,利用动态SQL实现交叉表查询
while @@fetch_status=0
begin
select @s=@s+',['+rtrim(@day)+'号'+类别+']=cast(sum(case when day(发生日期)='+rtrim(@day)+' and 类别='''+类别+''' then 数量*销售单价 else 0 end) as int)'
from (select distinct 类别 from yphjxshz) a
set @s=@s+',['+rtrim(@day)+'号合计]=cast(sum(case when day(发生日期)='+rtrim(@day)+' then 数量*销售单价 else 0 end) as int)'
fetch next from t_cursor into @day
end
--关闭游标
close t_cursor
deallocate t_cursor
--完成动态SQL语句的组织
set @s=@s+' from #t group by 医生名称'
--输出动态SQL语句的内容
print @s
--执行动态SQL
exec(@s)
end
GO
--创建存储过程
/*
write by cyg
date 2006-01-23
统计月医生工作量*/
CREATE procedure sp_getInfo(@month datetime,@time as int=0) --@time,统计时间中小时的限定值
as
begin
--输入参数合法性判定
if @time<0 or @time>23
return
declare @s varchar(8000),@day int
set @s='select 医生名称'
--提取被统计时间范围内的数据,并插入临时表#t 21 00:00:00为21号00点00分00秒而 20 23:59:59为下月20号的开始
select * into #t from yphjxshz where dateadd(hh,24-@time,发生日期) between convert(char(8),dateadd(mm,-1,@month),120)+'21 00:00:00' and convert(char(8),@month,120)+'20 23:59:59'
--定义游标,获取当前统计范围内不重复的日期数据信息
declare t_cursor cursor for select distinct day(发生日期) from yphjxshz order by day(发生日期)
--打开游标
open t_cursor
fetch next from t_cursor into @day
--遍历游标,利用动态SQL实现交叉表查询
while @@fetch_status=0
begin
select @s=@s+',['+rtrim(@day)+'号'+类别+']=cast(sum(case when day(发生日期)='+rtrim(@day)+' and 类别='''+类别+''' then 数量*销售单价 else 0 end) as int)'
from (select distinct 类别 from yphjxshz) a
set @s=@s+',['+rtrim(@day)+'号合计]=cast(sum(case when day(发生日期)='+rtrim(@day)+' then 数量*销售单价 else 0 end) as int)'
fetch next from t_cursor into @day
end
--关闭游标
close t_cursor
deallocate t_cursor
--完成动态SQL语句的组织
set @s=@s+' from #t group by 医生名称'
--输出动态SQL语句的内容
print @s
--执行动态SQL
exec(@s)
end
GO
select * into #t from yphjxshz where dateadd(hh,24-@time,发生日期) between cast(convert(char(8),dateadd(mm,-1,@month),120)+'21 00:00:00' as datetime) and cast(convert(char(8),@month,120)+'20 23:59:59' as datetime)
begin
Drop Table #T
end 这一句,你看看有没有必要加上~
declare t_cursor cursor for select distinct day(发生日期) from yphjxshz order by day(发生日期)
--打开游标
open t_cursor
fetch next from t_cursor into @day
--遍历游标,利用动态SQL实现交叉表查询
while @@fetch_status=0
begin
select @s=@s+',['+rtrim(@day)+'号'+类别+']=cast(sum(case when day(发生日期)='+rtrim(@day)+' and 类别='''+类别+''' then 数量*销售单价 else 0 end) as int)'
from (select distinct 类别 from yphjxshz) a
set @s=@s+',['+rtrim(@day)+'号合计]=cast(sum(case when day(发生日期)='+rtrim(@day)+' then 数量*销售单价 else 0 end) as int)'
fetch next from t_cursor into @day
end
--关闭游标
close t_cursor
deallocate t_cursor
--完成动态SQL语句的组织
set @s=@s+' from #t group by 医生名称'
declare t_cursor cursor for select distinct day(dateadd(hh,24-@time,发生日期)) from yphjxshz order by day(dateadd(hh,24-@time,发生日期))
--打开游标
open t_cursor
fetch next from t_cursor into @day
--遍历游标,利用动态SQL实现交叉表查询
while @@fetch_status=0
begin
select @s=@s+',['+rtrim(@day)+'号'+类别+']=cast(sum(case when day(dateadd(hh,24-'+ cast(@Time as varchar(10)) +',发生日期))='+rtrim(@day)+' and 类别='''+类别+''' then 数量*销售单价 else 0 end) as int)'
from (select distinct 类别 from yphjxshz) a
set @s=@s+',['+rtrim(@day)+'号合计]=cast(sum(case when day(dateadd(hh,24-'+ cast(@Time as varchar(10)) +',发生日期))='+rtrim(@day)+' then 数量*销售单价 else 0 end) as int)'
fetch next from t_cursor into @day
end
--关闭游标
close t_cursor
deallocate t_cursor
--完成动态SQL语句的组织
set @s=@s+' from #t group by 医生名称'
游标
用dateadd(hh,24-@time,发生日期) 查询效率低
write by cyg
date 2006-01-23
统计月医生工作量*/
CREATE procedure sp_getInfo(@month datetime,@time as int=0) --@time,统计时间中小时的限定值
as
begin
--输入参数合法性判定
if @time<0 or @time>23
return
declare @s varchar(8000),@day int
set @s='select 医生名称'
--提取被统计时间范围内的数据,并插入临时表#t 21 00:00:00为21号00点00分00秒而 20 23:59:59为下月20号的开始 -- haiwer 改下查询方式 select * into #t from yphjxshz where 发生日期 between dateadd(hh,@time-24,convert(char(8),dateadd(mm,-1,@month),120)+'21 00:00:00') and dateadd(hh,@time-24,convert(char(8),@month,120)+'20 23:59:59') -- haiwer 取代游标
select @s=@s+case when 类别='合计' then
',['+rtrim(iday)+'号合计]=cast(sum(case when day(dateadd(hh,24-'+ cast(@Time as varchar(10)) +',发生日期))='+rtrim(iday)+' then 数量*销售单价 else 0 end) as int)'
else
',['+rtrim(iday)+'号'+类别+']=cast(sum(case when day(dateadd(hh,24-'+ cast(@Time as varchar(10)) +',发生日期))='+rtrim(iday)+' and 类别='''+类别+''' then 数量*销售单价 else 0 end) as int)'
end
from
(select distinct day(dateadd(hh,24-@time,发生日期)) as iDay from yphjxshz ) as t,
(select distinct 类别,1 as ordertype from yphjxshz
union all select '合计' as 类别,2 as ordertype) as a
order by iday,ordertype
--完成动态SQL语句的组织
set @s=@s+' from #t group by 医生名称'
--输出动态SQL语句的内容
print @s
--执行动态SQL
exec(@s)
end
GO
都用了dateadd(hh,24-@time,发生日期)
仔细比较一下几天的数据,你自己的标准数据用
between '2006-5-23 15:00:00' and '2006-5-24 14:59:59'
得到24号数据
write by cyg
date 2006-01-23
统计月医生工作量*/
CREATE procedure sp_getInfo(@month datetime,@time as int=0) --@time,统计时间中小时的限定值
as
begin
--输入参数合法性判定
if @time<0 or @time>23
return
declare @s varchar(8000),@day int
set @s='select 医生名称'
--提取被统计时间范围内的数据,并插入临时表#t 21 00:00:00为21号00点00分00秒而 20 23:59:59为下月20号的开始 -- haiwer 改下查询方式 select * into #t from yphjxshz where 发生日期 between dateadd(hh,@time-24,convert(char(8),dateadd(mm,-1,@month),120)+'21 00:00:00') and dateadd(hh,@time-24,convert(char(8),@month,120)+'20 23:59:59') -- haiwer 取代游标
select @s=@s+case when 类别='合计' then
',['+rtrim(iday)+'号合计]=cast(sum(case when day(dateadd(hh,24-'+ cast(@Time as varchar(10)) +',发生日期))='+rtrim(iday)+' then 数量*销售单价 else 0 end) as int)'
else
',['+rtrim(iday)+'号'+类别+']=cast(sum(case when day(dateadd(hh,24-'+ cast(@Time as varchar(10)) +',发生日期))='+rtrim(iday)+' and 类别='''+类别+''' then 数量*销售单价 else 0 end) as int)'
end
from
(select distinct day(dateadd(hh,24-@time,发生日期)) as iDay from #T ) as t, --修改了这里
(select distinct 类别,1 as ordertype from #T --修改了这里
union all select '合计' as 类别,2 as ordertype) as a
order by iday,ordertype
--完成动态SQL语句的组织
set @s=@s+' from #t group by 医生名称'
--输出动态SQL语句的内容
print @s
--执行动态SQL
exec(@s)
end
GO