导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

这样的交叉表谁能给个存储过程

dm1cyg 2006-01-21 03:42:12
表结构如下:

姓名 varchar(20)
单号 varchar(20)
数量 money
单价 money
日期 datetime
类别 varchar(20)

数据是这样的
姓名 单号 数量 单价 日期 类别
------------------------------------------------------------------------
张大夫 200510112 2 55 2006-1-1 12:30 :30 中药
李大夫 200601111 1 100 2006-1-1 13:33:30 西药
李大夫 200601112 1 100 2006-1-2 16:33:30 草药
王大夫 200601114 4 100 2006-1-1 3:33:30 西药
张大夫 200601115 1 400 2006-1-3 11:33:30 中药
李大夫 200601114 1 100 2006-1-8 13:33:30 西药
李大夫 200601155 1 100 2006-1-9 13:33:30 西药
李大夫 200601145 1 100 2006-1-1 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点就算第二天的了,如何统计呀



























...全文
154 点赞 收藏 8
写回复
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
mislrb 2006-01-22
那我本月就30天怎么办

------------------------------------------------------------------
你说的这个问题不是一个问题,很容易解决,如果你说你做不到,可以帮你,31只是取的最大值
至于3点是按你的要求,问问题应该交待清楚,而不要让别人去猜,要改也不难,给一个方法你,只是指个方向,具体的还是要看你自己怎么变通
回复
子陌红尘 2006-01-22
统计指定月份数据报表的存储过程:
------------------------------------------------------------------------------------------------------------------------------

--生成测试数据
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
回复
子陌红尘 2006-01-22
--生成测试数据
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
回复
云中客 2006-01-22
楼主,如果你要这样出报表的话,这个报表要用多大的纸来打印
回复
dm1cyg 2006-01-22
上面的代码还通不过
我是想如过我查2月分的就是一月21号下午3点到二月20下午3点的金额,而且这个3点的时间我应该让用户自己设定,也可能是5点呀,或12点
回复
dm1cyg 2006-01-21
那我本月就30天怎么办
回复
mislrb 2006-01-21
改成过程:
create procedure p_getNew
@stdate datetime,
@endate datetime
as
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@i int
select @s1='',@s2='',@s3='',@i=1
select 姓名,单号,数量,单价,
日期=case when datediff(ss,日期,convert(datetime,convert(varchar(10),日期,120)+'14:59:59.997'),120)>0 then dateadd(dd,1,日期) else 日期 end,类别 into t_a from yourtable where 日期 between @stdate and @endate order by 日期

while @i<=31
begin
select @s3=@s3+',['+cast(@i as varchar(2))+'号'+类别+']=isnull(sum(case when 类别='''+类别+''' and datepart(day,日期)='+cast(@i as varchar(2))+' then 数量*单价 end),0)' from t_a group by 类别
select @s3=@s3+',['+cast(@i as varchar(2))+'号合计]=isnull(sum(case when datepart(day,日期)='+cast(@i as varchar(2))+' then 数量*单价 end),0)'
if @i=10 select @s1=@s3,@s3=''
if @i=20 select @s2=@s3,@s3=''
set @i=@i+1
end
exec('select 姓名'+@s1+@s2+@s3+' from t_a group by 姓名')
go

--调用过程,计算这个月的
exec dbo.p_getNew '2005-12-20 14:59:59.997','2005-12-20 14:59:59.977'
回复
mislrb 2006-01-21
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','西药'

declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@i int
select @s1='',@s2='',@s3='',@i=1
while @i<=31
begin
select @s3=@s3+',['+cast(@i as varchar(2))+'号'+类别+']=isnull(sum(case when 类别='''+类别+''' and datepart(day,日期)='+cast(@i as varchar(2))+' then 数量*单价 end),0)' from t_a group by 类别
select @s3=@s3+',['+cast(@i as varchar(2))+'号合计]=isnull(sum(case when datepart(day,日期)='+cast(@i as varchar(2))+' then 数量*单价 end),0)'
if @i=10 select @s1=@s3,@s3=''
if @i=20 select @s2=@s3,@s3=''
set @i=@i+1
end
exec('select 姓名'+@s1+@s2+@s3+' from t_a group by 姓名')
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告