ALTER proc TFPOS_qry
@OS_DD varchar(7)
as
declare @s varchar(8000),@dt datetime,@dt1 varchar(10)
set nocount on
select @s='',@dt=dateadd(month,1,@os_dd+'-01')-1
,@dt1=convert(varchar(10),@dt,120)
select @s=@s+',UPM'+id+'单价=max(case mm when '+id+' then UP else 0 end)'+
',UPM1'+id+'金额=sum(case mm when '+id+' then AMT else 0 end)'from( select id='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'
union all select '10' union all select '11' union all select '12'
)a where id<=month(@dt)
exec('select a.PRD_NO'+@s+'
from TF_POS a join(
select PRD_NO,mm=month(OS_DD),OS_DD=max(OS_DD)
from TF_POS
where OS_DD<='''+@dt1+'''
group by PRD_NO,month(OS_DD)
)b on a.PRD_NO=b.PRD_NO and a.OS_DD=b.OS_DD
group by a.PRD_NO
')
@OS_DD varchar(7)
as
declare @s varchar(8000),@dt datetime,@dt1 varchar(10)
set nocount on
select @s='',@dt=dateadd(month,1,@os_dd+'-01')-1
,@dt1=convert(varchar(10),@dt,120)
select @s=@s+',UPM'+id+'单价=max(case mm when '+id+' then UP else 0 end)'+
',UPM1'+id+'金额=sum(case mm when '+id+' then AMT else 0 end)'from( select id='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'
union all select '10' union all select '11' union all select '12'
)a where id<=month(@dt)
exec('select a.PRD_NO'+@s+'
from TF_POS a join(
select PRD_NO,mm=month(OS_DD),OS_DD=max(OS_DD)
from TF_POS
where OS_DD<='''+@dt1+'''
group by PRD_NO,month(OS_DD)
)b on a.PRD_NO=b.PRD_NO and a.OS_DD=b.OS_DD
group by a.PRD_NO
')
--查询的存储过程
create proc p_qry
@OS_DD varchar(7)
as
declare @s varchar(8000),@dt datetime,@dt1 varchar(10)
set nocount on
select @s='',@dt=dateadd(month,1,@os_dd+'-01')-1
,@dt1=convert(varchar(10),@dt,120)
select @s=@s+',UPM'+id+'=max(case mm when '+id+' then UP else 0 end)'
+',ATM'+id+'=sum(case mm when '+id+' then AMT else 0 end)'
from(
select id='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'
union all select '10' union all select '11' union all select '12'
)a where id<=month(@dt)
exec('select a.PRD_NO'+@s+'
from TF_POS a join(
select PRD_NO,mm=month(OS_DD),OS_DD=max(OS_DD)
from TF_POS
where OS_DD<='''+@dt1+'''
group by PRD_NO,month(OS_DD)
)b on a.PRD_NO=b.PRD_NO and a.OS_DD=b.OS_DD
group by a.PRD_NO
')
create table TF_POS(PRD_NO char(10),OS_DD datetime,UP decimal(20,4),QTY int,AMT money)
insert TF_POS select '2310031002','2003-1-2',0.053,500,26.5
union all select '2310031002','2003-1-4',2.5,200,500
union all select '2310031002','2003-1-4',0.04,2500,100
union all select '1121000191','2003-1-11',0.055,300,16.5
union all select '2310031002','2003-2-1',0.046,40000,1840
union all select '1121000191','2003-2-13',0.23,6000,1380
union all select '1121000191','2003-2-15',0.197,4000,788
union all select '1121000191','2003-3-11',0.044,4000,176
union all select '1121000191','2003-3-9',0.0066,30000,198
union all select '1121000191','2003-3-15',0.35,20000,7000
union all select '2310031002','2003-4-15',1.9,10000,19000
union all select '1121000191','2003-4-15',0.04,100,4
union all select '1121000191','2003-4-15',0.0066,20,0.132
go--查询的存储过程
create proc p_qry
@OS_DD varchar(7)
as
declare @s varchar(8000),@dt datetime,@dt1 varchar(10)
set nocount on
select @s='',@dt=dateadd(month,1,@os_dd+'-01')-1
,@dt1=convert(varchar(10),@dt,120)
select @s=@s+',UPM'+id+'=max(case mm when '+id+' then UP else 0 end)'
+',ATM'+id+'=sum(case mm when '+id+' then AMT else 0 end)'
from(
select id='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'
union all select '10' union all select '11' union all select '12'
)a where id<=month(@dt)
exec('select a.PRD_NO'+@s+'
from TF_POS a join(
select PRD_NO,mm=month(OS_DD),OS_DD=max(OS_DD)
from TF_POS
where OS_DD<='''+@dt1+'''
group by PRD_NO,month(OS_DD)
)b on a.PRD_NO=b.PRD_NO and a.OS_DD=b.OS_DD
group by a.PRD_NO
')
go--调用
exec p_qry '2003-03'
go--删除测试环境
drop table TF_POS
drop proc p_qry/*--测试结果
PRD_NO UPM1 ATM1 UPM2 ATM2 UPM3 ATM3
---------- ---------------------- --------------------- ---------------------- --------------------- ---------------------- ---------------------
1121000191 .0550 16.5000 .1970 788.0000 .3500 7000.0000
2310031002 2.5000 600.0000 .0460 1840.0000 .0000 .0000
--*/
drop table TF_POS
drop proc p_qry
create table TF_POS(PRD_NO char(10),OS_DD datetime,UP decimal(20,4),QTY decimal(20,4), AMT decimal(20,4))
insert TF_POS select '2310031002','2003-1-2',0.053,500,26.5
union all select '2310031002','2003-1-4',2.5,200,500
union all select '2310031002','2003-1-4',0.04,2500,100
union all select '1121000191','2003-1-11',0.055,300 , 16.5
union all select '2310031002','2003-2-1',0.046,40000 , 1840
union all select '1121000191','2003-2-13',0.23,6000 , 1380
union all select '1121000191','2003-2-15',0.197,4000 , 788
union all select '1121000191','2003-3-11',0.044,4000, 176
union all select '1121000191','2003-3-9',0.0066,30000 , 198
union all select '1121000191','2003-3-15',0.35,20000, 7000
union all select '2310031002','2003-4-15',1.9,10000 , 19000
union all select '1121000191','2003-4-15',0.04,100 , 4
union all select '1121000191','2003-4-15',0.0066,20 , 0.132 --查询的存储过程
create proc p_qry
@OS_DD varchar(7)
as
declare @s varchar(8000),@dt datetime,@dt1 varchar(10)
set nocount on
select @s='',@dt=dateadd(month,1,@os_dd+'-01')-1
,@dt1=convert(varchar(10),@dt,120)
select @s=@s+',UPM'+id+'月最后单价=max(case mm when '+id+' then UP else 0 end)'+
',UPM'+id+'月总金额=sum(case mm when '+id+' then AMT else 0 end)'
from(
select id='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'
union all select '10' union all select '11' union all select '12'
)a where id<=month(@dt)
exec('select a.PRD_NO'+@s+'
from TF_POS a join(
select PRD_NO,mm=month(OS_DD),OS_DD=max(OS_DD)
from TF_POS
where OS_DD<='''+@dt1+'''
group by PRD_NO,month(OS_DD)
)b on a.PRD_NO=b.PRD_NO and a.OS_DD=b.OS_DD
group by a.PRD_NO
')
go
PRD_NO UPM1月最后单价 UPM1月总金额 UPM2月最后单价 UPM2月总金额 UPM3月最后单价 UPM3月总金额
---------- ---------------------- ---------------------------------------- ---------------------- ---------------------------------------- ---------------------- ----------------------------------------
1121000191 .0550 16.5000 .1970 788.0000 .3500 7000.0000
2310031002 2.5000 600.0000 .0460 1840.0000 .0000 .0000