--测试环境
create table 表A ([goods(物料)] varchar(10),出入库类别 varchar(10),数量 int ,单价 money ,金额 money ,日期 datetime)
insert into 表A select 'A','B',20,0.5,10,null
union all select 'A','1',10,0.5,5,'2005-09-01'
union all select 'A','2',5,0.5,2.5,'2005-09-03'
union all select 'A','1',20,0.5,10,'2005-09-04'
union all select 'A','6',10,0.5,5,'2005-09-05'
union all select 'B','1',10,0.5,5,'2005-09-02'create table 表B (类型 varchar(10),名称 varchar(10))
insert into 表B select 'B','期初'
union all select '1','采购入库'
union all select '2','生产入库'
union all select '6','销售出库'
union all select '7','外加工出库'
--创建函数
ALTER Function F_GetMoney(@物料名称 varchar(10),@startTime datetime)
returns varchar(10)
as
begin
declare @数量 int,@单价 money,@金额 money
select @数量=a.数量+b.数量 from 表A a,(select 数量 from 表A where 日期< ''+@startTime+'') b where [goods(物料)]=@物料名称 and 出入库类别='B'
select top 1 @单价= 单价 from 表A where [goods(物料)]=@物料名称
select @金额=@数量*@单价
return convert(varchar(10),@数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
endALTER Function F_GetMoney1(@物料名称 varchar(10),@startTime datetime,@EndTime datetime)
returns varchar(10)
as
begin
declare @数量 int,@单价 money,@金额 money
select @数量=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='1' or 出入库类别='2'
select top 1 @单价= 单价 from 表A where [goods(物料)]=@物料名称
select @金额=@数量*@单价
return convert(varchar(10),@数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
endALTER Function F_GetMoney2(@物料名称 varchar(10),@startTime datetime,@EndTime datetime)
returns varchar(10)
as
begin
declare @数量 int,@单价 money,@金额 money
select @数量=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='6'
select top 1 @单价= 单价 from 表A where [goods(物料)]=@物料名称
select @金额=@数量*@单价
return convert(varchar(10),@数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
endAlter Function F_GetMoney3(@物料名称 varchar(10),@startTime datetime,@EndTime datetime)
returns varchar(10)
as
begin
declare @数量初期 int ,@数量收入 int,@数量销售 int,@结存数量 int,@单价 money,@金额 money
select @数量初期=a.数量+b.数量 from 表A a,(select 数量 from 表A where 日期< ''+@startTime+'') b where [goods(物料)]=@物料名称 and 出入库类别='B'
select @数量收入=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='1' or 出入库类别='2'
select @数量销售=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='6' select @结存数量=@数量初期+@数量收入-@数量销售
select top 1 @单价= 单价 from 表A where [goods(物料)]=@物料名称
select @金额=@结存数量*@单价
return convert(varchar(10),@结存数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
end
--查询
select 物料=[goods(物料)],
[期初结存(数量,单价,金额)]=dbo.F_GetMoney([goods(物料)],'2005-09-02'),
[本期收入(数量,单价,金额)]=dbo.F_GetMoney1([goods(物料)],'2005-09-02','2005-09-05'),
[本期发出(数量,单价,金额)]=dbo.F_GetMoney2([goods(物料)],'2005-09-02','2005-09-05'),
[本期结存(数量,单价,金额)]=dbo.F_GetMoney3([goods(物料)],'2005-09-02','2005-09-05') from 表A
where [goods(物料)]='A'
group by [goods(物料)]
--结果
物料 期初结存(数量,单价,金额) 本期收入(数量,单价,金额) 本期发出(数量,单价,金额) 本期结存(数量,单价,金额)
---------- -------------- -------------- -------------- --------------
A 30,0.50,15 25,0.50,12 10,0.50,5. 45,0.50,22
create table 表A ([goods(物料)] varchar(10),出入库类别 varchar(10),数量 int ,单价 money ,金额 money ,日期 datetime)
insert into 表A select 'A','B',20,0.5,10,null
union all select 'A','1',10,0.5,5,'2005-09-01'
union all select 'A','2',5,0.5,2.5,'2005-09-03'
union all select 'A','1',20,0.5,10,'2005-09-04'
union all select 'A','6',10,0.5,5,'2005-09-05'
union all select 'B','1',10,0.5,5,'2005-09-02'create table 表B (类型 varchar(10),名称 varchar(10))
insert into 表B select 'B','期初'
union all select '1','采购入库'
union all select '2','生产入库'
union all select '6','销售出库'
union all select '7','外加工出库'
--创建函数
ALTER Function F_GetMoney(@物料名称 varchar(10),@startTime datetime)
returns varchar(10)
as
begin
declare @数量 int,@单价 money,@金额 money
select @数量=a.数量+b.数量 from 表A a,(select 数量 from 表A where 日期< ''+@startTime+'') b where [goods(物料)]=@物料名称 and 出入库类别='B'
select top 1 @单价= 单价 from 表A where [goods(物料)]=@物料名称
select @金额=@数量*@单价
return convert(varchar(10),@数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
endALTER Function F_GetMoney1(@物料名称 varchar(10),@startTime datetime,@EndTime datetime)
returns varchar(10)
as
begin
declare @数量 int,@单价 money,@金额 money
select @数量=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='1' or 出入库类别='2'
select top 1 @单价= 单价 from 表A where [goods(物料)]=@物料名称
select @金额=@数量*@单价
return convert(varchar(10),@数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
endALTER Function F_GetMoney2(@物料名称 varchar(10),@startTime datetime,@EndTime datetime)
returns varchar(10)
as
begin
declare @数量 int,@单价 money,@金额 money
select @数量=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='6'
select top 1 @单价= 单价 from 表A where [goods(物料)]=@物料名称
select @金额=@数量*@单价
return convert(varchar(10),@数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
endAlter Function F_GetMoney3(@物料名称 varchar(10),@startTime datetime,@EndTime datetime)
returns varchar(10)
as
begin
declare @数量初期 int ,@数量收入 int,@数量销售 int,@结存数量 int,@单价 money,@金额 money
select @数量初期=a.数量+b.数量 from 表A a,(select 数量 from 表A where 日期< ''+@startTime+'') b where [goods(物料)]=@物料名称 and 出入库类别='B'
select @数量收入=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='1' or 出入库类别='2'
select @数量销售=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='6' select @结存数量=@数量初期+@数量收入-@数量销售
select top 1 @单价= 单价 from 表A where [goods(物料)]=@物料名称
select @金额=@结存数量*@单价
return convert(varchar(10),@结存数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
end
--查询
select 物料=[goods(物料)],
[期初结存(数量,单价,金额)]=dbo.F_GetMoney([goods(物料)],'2005-09-02'),
[本期收入(数量,单价,金额)]=dbo.F_GetMoney1([goods(物料)],'2005-09-02','2005-09-05'),
[本期发出(数量,单价,金额)]=dbo.F_GetMoney2([goods(物料)],'2005-09-02','2005-09-05'),
[本期结存(数量,单价,金额)]=dbo.F_GetMoney3([goods(物料)],'2005-09-02','2005-09-05') from 表A
where [goods(物料)]='A'
group by [goods(物料)]
--结果
物料 期初结存(数量,单价,金额) 本期收入(数量,单价,金额) 本期发出(数量,单价,金额) 本期结存(数量,单价,金额)
---------- -------------- -------------- -------------- --------------
A 30,0.50,15 25,0.50,12 10,0.50,5. 45,0.50,22
--测试环境
--测试环境
create table 表A ([goods(物料)] varchar(10),出入库类别 varchar(10),数量 int ,单价 money ,金额 money ,日期 datetime)
insert into 表A select 'A','B',20,0.5,10,null
union all select 'A','1',10,0.5,5,'2005-09-01'
union all select 'A','2',5,0.5,2.5,'2005-09-03'
union all select 'A','1',20,0.5,10,'2005-09-04'
union all select 'A','6',10,0.5,5,'2005-09-05'
union all select 'B','1',10,0.5,5,'2005-09-02'create table 表B (类型 varchar(10),名称 varchar(10))
insert into 表B select 'B','期初'
union all select '1','采购入库'
union all select '2','生产入库'
union all select '6','销售出库'
union all select '7','外加工出库'--建立函数
Create Function F_GetMoney(@物料名称 varchar(10),@startTime datetime,@EndTime datetime,@Flag int)
returns varchar(10)
as
begin
declare @数量 int,@单价 money,@金额 money,@返回值 varchar(10)
select top 1 @单价= 单价 from 表A where [goods(物料)]=@物料名称
if @Flag=1 --[期初结存(数量,单价,金额)]
begin
select @数量=a.数量+b.数量 from 表A a,(select 数量 from 表A where 日期< ''+@startTime+'') b where [goods(物料)]=@物料名称 and 出入库类别='B'
select @金额=@数量*@单价
select @返回值=convert(varchar(10),@数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
endif @Flag=2 --[本期收入(数量,单价,金额)]
begin
select @数量=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='1' or 出入库类别='2'
select @金额=@数量*@单价
select @返回值=convert(varchar(10),@数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
endif @Flag=3 --[本期发出(数量,单价,金额)]
begin
select @数量=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='6'
select @金额=@数量*@单价
select @返回值=convert(varchar(10),@数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
endif @Flag=4 --[本期结存(数量,单价,金额)]
begin
declare @数量收入 int,@数量销售 int,@结存数量 int,@结存单价 money,@本期结存金额 money
select @数量=a.数量+b.数量 from 表A a,(select 数量 from 表A where 日期< ''+@startTime+'') b where [goods(物料)]=@物料名称 and 出入库类别='B'
select @数量收入=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='1' or 出入库类别='2'
select @数量销售= sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='6' select @结存数量=@数量+@数量收入-@数量销售
select top 1 @结存单价= 单价 from 表A where [goods(物料)]=@物料名称
select @金额=@结存数量*@结存单价
select @返回值=convert(varchar(10),@结存数量)+','+convert(varchar(10),@结存单价)+','+convert(varchar(10),@金额)
end
if @Flag=5 --采购入库
begin
select @数量=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='1'
select @金额=@数量*@单价
select @返回值=convert(varchar(10),@数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
end
if @Flag=6 --生产入库
begin
select @数量=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='2'
select @金额=@数量*@单价
select @返回值=convert(varchar(10),@数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
endif @Flag=7 --入库合计
begin
declare @合计数量 int,@合计数量1 int,@合计金额 money,@合计金额1 money,@合计入库 money
select @合计数量=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='1'
select @合计数量1=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='2'
select @合计入库=(@合计数量+@合计数量1)*@单价
select @返回值=convert(varchar(10),@合计数量+@合计数量1)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@合计入库)
endif @Flag=8 --销售出库
begin select @数量=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='6'
select @金额=@数量*@单价
select @返回值=convert(varchar(10),@数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
endif @Flag=9 --外加工出库
begin select @数量=Isnull(sum(数量),0) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='7'
select @金额=@数量*@单价
select @返回值=case when @数量*@单价=0 then 0 else
convert(varchar(10),@数量)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额) End
endif @Flag=10 --出库合计
begin
declare @出库数量 int, @出库数量1 int
select @出库数量=sum(数量) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='6'
select @出库数量1=Isnull(sum(数量),0) from 表A where 日期 between ''+@startTime+'' and ''+@EndTime+'' and [goods(物料)]=@物料名称 and 出入库类别='7'
select @金额=(@出库数量+@出库数量1)*@单价
select @返回值=convert(varchar(10),@出库数量+@出库数量1)+','+convert(varchar(10),@单价)+','+convert(varchar(10),@金额)
end
return @返回值
end--问题1
--查询
select 物料=[goods(物料)],
[期初结存(数量,单价,金额)]=dbo.F_GetMoney([goods(物料)],'2005-09-02','2005-09-05',1),
[本期收入(数量,单价,金额)]=dbo.F_GetMoney([goods(物料)],'2005-09-02','2005-09-05',2),
[本期发出(数量,单价,金额)]=dbo.F_GetMoney([goods(物料)],'2005-09-02','2005-09-05',3),
[本期结存(数量,单价,金额)]=dbo.F_GetMoney([goods(物料)],'2005-09-02','2005-09-05',4) from 表A
where [goods(物料)]='A'
group by [goods(物料)]
--结果
物料 期初结存(数量,单价,金额) 本期收入(数量,单价,金额) 本期发出(数量,单价,金额) 本期结存(数量,单价,金额)
---------- -------------- -------------- -------------- --------------
A 30,0.50,15 25,0.50,12 10,0.50,5. 45,0.50,22--问题2
--查询
select 物料=[goods(物料)],
[期初结存]=dbo.F_GetMoney([goods(物料)],'2005-09-02','2005-09-05',1),
[采购入库]=dbo.F_GetMoney([goods(物料)],'2005-09-02','2005-09-05',5),
[生产入库]=dbo.F_GetMoney([goods(物料)],'2005-09-02','2005-09-05',6),
[入库合计]=dbo.F_GetMoney([goods(物料)],'2005-09-02','2005-09-05',7),
[销售出库]=dbo.F_GetMoney([goods(物料)],'2005-09-02','2005-09-05',8),
[外加工出库]=dbo.F_GetMoney([goods(物料)],'2005-09-02','2005-09-05',9),
[出库合计]=dbo.F_GetMoney([goods(物料)],'2005-09-02','2005-09-05',10),
[期末结存]=dbo.F_GetMoney([goods(物料)],'2005-09-02','2005-09-05',4)
from 表A
where [goods(物料)]='A'
group by [goods(物料)]
--结果
物料 期初结存 采购入库 生产入库 入库合计 销售出库 外加工出库 出库合计 期末结存
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
A 30,0.50,15 20,0.50,10 5,0.50,2.5 25,0.50,12 10,0.50,5. 0 10,0.50,5. 45,0.50,22(所影响的行数为 1 行)--删除测试环境
Drop table 表A,表B
Drop Function F_GetMoney--可以参考下