给我弄迷糊了

[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点的，日期最好也能定义

[lsqkeke]

用分段组成SQL可以实现的：
我看了你的显示格式：
数据1号中药 100怎么得来的，数量和单价相乘也不得这个数啊！

[lsqkeke]

给你个例子子先：
CREATE TABLE tb(Year int,Quarter int,Quantity decimal(10,1),Price decimal(10,2))
INSERT tb SELECT 1990, 1, 1.1, 2.5
UNION ALL SELECT 1990, 1, 1.2, 3.0
UNION ALL SELECT 1990, 2, 1.2, 3.0
UNION ALL SELECT 1990, 1, 1.3, 3.5
UNION ALL SELECT 1990, 2, 1.4, 4.0
UNION ALL SELECT 1991, 1, 2.1, 4.5
UNION ALL SELECT 1991, 2, 2.1, 4.5
UNION ALL SELECT 1991, 2, 2.2, 5.0
UNION ALL SELECT 1991, 1, 2.3, 5.5
UNION ALL SELECT 1991, 1, 2.4, 6.0

--查询处理
DECLARE @s nvarchar(4000)
--交叉报表处理代码头
SET @s='SELECT Year'
--生成列记录水平显示的处理代码拼接
SELECT @s=@s
+','+QUOTENAME(N'Q'+CAST(Quarter as varchar)+N'_Amount')
+N'=SUM(CASE Quarter WHEN '+CAST(Quarter as varchar)
+N' THEN Quantity END)'
+','+QUOTENAME(N'Q'+CAST(Quarter as varchar)+N'_Price')
+N'=CAST(AVG(CASE Quarter WHEN '+CAST(Quarter as varchar)
+N' THEN Price END)AS DECIMAL(10,2))'
+','+QUOTENAME(N'Q'+CAST(Quarter as varchar)+N'_Money')
+N'=CAST(SUM(CASE Quarter WHEN '+CAST(Quarter as varchar)
+N' THEN Quantity*Price END)AS DECIMAL(10,2))'
FROM tb
GROUP BY Quarter
--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句
EXEC(@s+N'
FROM tb
GROUP BY Year')
/*--结果
Year Q1_Amount Q1_Price Q1_Money Q2_Amount Q2_Price Q2_Money
------- ----------------- --------------- ----------------- ----------------- -------------- ----------------
1990 3.6 3.00 10.90 2.6 3.50 9.20
1991 6.8 5.33 36.50 4.3 4.75 20.45
--*/

[子陌红尘]

--生成测试数据
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

[子陌红尘]

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

--生成测试数据
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