我认为都是绝症了

[dm1cyg]

create table t_a(姓名 varchar(6),单号 varchar(9),数量 money,单价 money,日期 datetime,类别 varchar(4))
insert t_a
select '张大夫','200510112',2,55,'2006-01-01 12:30:30','中药' union all
select '李大夫','200601111',1,100,'2006-01-01 13:33:30','西药' union all
select '李大夫','200601112',1,100,'2006-01-02 16:33:30','草药' union all
select '王大夫','200601114',4,100,'2006-01-01 3:33:30','西药' union all
select '张大夫','200601115',1,400,'2006-01-03 11:33:30','中药' union all
select '李大夫','200601114',1,100,'2006-01-08 13:33:30','西药' union all
select '李大夫','200601155',1,100,'2006-01-09 13:33:30','西药' union all
select '李大夫','200601145',1,100,'2006-01-01 13:33:30','西药'


我想得到如下表格
------------------------------------------------------------------------
医生名称 1号中药 | 1号西药| 1号草药 | 1号合计|2号中药 |2号西药 |2号草药 |2号合计...
-------------------------------------------------|----------------------------------
张大夫 100 20 50 170 | 0 50 30 80
-------------------------------------------------|-----------------------------------
...
我想每个月20号下午3点结帐，20号3点以后的就是下个月的数据了，并且每天日期也是下午3点为分界的，过了3点就算第二天的了，如何统计呀?而且3点或几点都是用户自己来定的，也可能5点或10点什么的，比如我统计2月份的就是1月21号下午3点到2月20好下午3点的，日期最好也能定义

[子陌红尘]

--生成测试数据
create table t_a(姓名 varchar(6),单号 varchar(9),数量 money,单价 money,日期 datetime,类别 varchar(4))
insert t_a
select '张大夫','200510112',2,55,'2006-01-01 12:30:30','中药' union all
select '李大夫','200601111',1,100,'2006-01-01 13:33:30','西药' union all
select '李大夫','200601112',1,100,'2006-01-02 16:33:30','草药' union all
select '王大夫','200601114',4,100,'2006-01-01 3:33:30','西药' union all
select '张大夫','200601115',1,400,'2006-01-03 11:33:30','中药' union all
select '李大夫','200601114',1,100,'2006-01-08 13:33:30','西药' union all
select '李大夫','200601155',1,100,'2006-01-09 13:33:30','西药' union all
select '李大夫','200601145',1,100,'2006-01-01 13:33:30','西药'
go


--创建存储过程
create procedure sp_getInfo(@time int)
as
begin
declare @s varchar(8000),@day int
set @s='select 姓名'

--提取被统计时间范围内的数据，并插入临时表#t
select * into #t from t_a where dateadd(hh,24-@time,日期) between convert(char(8),dateadd(mm,-1,getdate()),120)+'21 00:00:00' and convert(char(8),getdate(),120)+'20 23:59:59'

--定义游标，获取当前统计范围内不重复的日期数据信息
declare t_cursor cursor for select distinct day(日期) from t_a 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 t_a) 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


--调用存储过程，输出结果楼主自己看
exec sp_getinfo 15
go


--清除测试环境
drop procedure sp_getinfo
drop table t_a

[子陌红尘]

修改一处：
------------------------------------------------------------------------------------------------------------------------------------

--生成测试数据
create table t_a(姓名 varchar(6),单号 varchar(9),数量 money,单价 money,日期 datetime,类别 varchar(4))
insert t_a
select '张大夫','200510112',2, 55,'2006-01-01 12:30:30','中药' union all
select '李大夫','200601111',1,100,'2006-01-01 13:33:30','西药' union all
select '李大夫','200601112',1,100,'2006-01-02 16:33:30','草药' union all
select '王大夫','200601114',4,100,'2006-01-01 03:33:30','西药' union all
select '张大夫','200601115',1,400,'2006-01-03 11:33:30','中药' union all
select '李大夫','200601114',1,100,'2006-01-08 13:33:30','西药' union all
select '李大夫','200601155',1,100,'2006-01-09 13:33:30','西药' union all
select '李大夫','200601145',1,100,'2006-01-01 13:33:30','西药'
go


--创建存储过程
create procedure sp_getInfo(@time as int=0) --@time，统计时间中小时的限定值
as
begin
--输入参数合法性判定
if @time<0 or @time>23
return

declare @s varchar(8000),@day int
set @s='select 姓名'

--提取被统计时间范围内的数据，并插入临时表#t
select * into #t from t_a where dateadd(hh,24-@time,日期) between convert(char(8),dateadd(mm,-1,getdate()),120)+'21 00:00:00' and convert(char(8),getdate(),120)+'20 23:59:59'

--定义游标，获取当前统计范围内不重复的日期数据信息
declare t_cursor cursor for select distinct day(日期) from t_a 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 t_a) 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


--调用存储过程，输出结果楼主自己看
exec sp_getinfo 15
go


--清除测试环境
drop procedure sp_getinfo
drop table t_a

[OracleRoob]

libin_ftsafe(子陌红尘) ：老大真快啊

[子陌红尘]

统计指定月份数据报表的存储过程：
------------------------------------------------------------------------------------------------------------------------------

--生成测试数据
create table t_a(姓名 varchar(6),单号 varchar(9),数量 money,单价 money,日期 datetime,类别 varchar(4))
insert t_a
select '张大夫','200510112',2, 55,'2006-01-01 12:30:30','中药' union all
select '李大夫','200601111',1,100,'2006-01-01 13:33:30','西药' union all
select '李大夫','200601112',1,100,'2006-01-02 16:33:30','草药' union all
select '王大夫','200601114',4,100,'2006-01-01 03:33:30','西药' union all
select '张大夫','200601115',1,400,'2006-01-03 11:33:30','中药' union all
select '李大夫','200601114',1,100,'2006-01-08 13:33:30','西药' union all
select '李大夫','200601155',1,100,'2006-01-09 13:33:30','西药' union all
select '李大夫','200601145',1,100,'2006-01-01 13:33:30','西药'
go


--创建存储过程
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
select * into #t from t_a 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 t_a 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 t_a) 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


--调用存储过程，输出结果楼主自己看
exec sp_getinfo '2006-01-01',15
go


--清除测试环境
drop procedure sp_getinfo
drop table t_a

[OracleRoob]

500分啊，眼馋...