该存储过程是在两张视图的基础上建立的。
视图1:V_mayerial_in_1 字段有date1(入库年月),code,name,spec,unit,InAmount(入库数量),Inmoney(所对应的入库金额)
视图2:V_material_out_1 字段有date1(出库年月),code,name,spec,unit,OutAmount(出库数量)
通过存储过程来计算显示:所对应的出库金额。
计算公式如下:
1,本月结存数量=上月结存数量+本月入库数量-本月出库数量
2,本月结存金额=((上月结存金额+本月入库金额)/(上月结存数量+本月入库数量))*本月结存数量
3,本月出库金额=上月结存金额+本月入库金额-本月结存金额
具体存储过程如下:
CREATE PROCEDURE p_total @Yearmonth Varchar(8)
AS
set nocount on
declare @dt1 datetime,@dt2 datetime
select @dt1=@Yearmonth +'01',@dt2=dateadd(month,1,@dt1)-1
select Date1 =@Yearmonth
,code=isnull(V_mayerial_in_1.code,V_material_out_1.code)
,name=isnull(V_mayerial_in_1.name,V_material_out_1.name)
,Spec=isnull(V_mayerial_in_1.Spec,V_material_out_1.Spec)
,Unit=isnull(V_mayerial_in_1.Unit,V_material_out_1.Unit)
,上月结存数量=isnull(期初入库数量,0)-isnull(期初出库数量,0)
,上月结存金额=isnull(期初收入金额,0){问题就在这里,其实这里:上月结存金额=isnull(期初收入金额,0)-isnull(期初出库金额,0)
这里的期初出库金额---也就是上个月的出库金额,问题就是存储过程如何自己调用自己的上个月的出库金额!!!}
,本月结存金额=(isnull(期初收入金额,0)+isnull(Inmoney,0))/(isnull(期初入库数量,0)-isnull(期初出库数量,0)+isnull(InAmount,0))*(isnull(期初入库数量,0)-isnull(期初出库数量,0))
,本月入库数量=isnull(InAmount,0),本月出库=isnull(OutAmount,0)
,本月收入金额=isnull(Inmoney,0)
,本月结存数量=isnull(期初入库数量,0)-isnull(期初出库数量,0)
+isnull(InAmount,0)-isnull(OutAmount,0)
,本月发出金额=isnull(期初收入金额,0)+isnull(Inmoney,0)-(isnull(期初收入金额,0)+isnull(Inmoney,0))/(isnull(期初入库数量,0)-isnull(期初出库数量,0)+isnull(InAmount,0))*(isnull(期初入库数量,0)-isnull(期初出库数量,0))
from(
select code=isnull(V_mayerial_in_1.code,V_material_out_1.code)
,name=isnull(V_mayerial_in_1.name,V_material_out_1.name)
,Spec=isnull(V_mayerial_in_1.Spec,V_material_out_1.Spec)
,Unit=isnull(V_mayerial_in_1.Unit,V_material_out_1.Unit)
,InAmount,期初入库数量,Inmoney,期初收入金额
from(
select code,name,Spec,unit,InAmount,Inmoney
from V_mayerial_in_1
where Date1 between @dt1 and @dt2
group by code,name,Spec,unit,InAmount,Inmoney
)V_mayerial_in_1 full join(
select code,name,Spec,unit,期初入库数量=sum(InAmount),期初收入金额=sum(Inmoney)
from V_mayerial_in_1
where date1<@dt1
group by code,name,Spec,unit
)V_material_out_1 on V_mayerial_in_1.code=V_material_out_1.code
)V_mayerial_in_1 full join(
select code=isnull(V_mayerial_in_1.code,V_material_out_1.code)
,name=isnull(V_mayerial_in_1.name,V_material_out_1.name)
,Spec=isnull(V_mayerial_in_1.Spec,V_material_out_1.Spec)
,Unit=isnull(V_mayerial_in_1.Unit,V_material_out_1.Unit)
,OutAmount,期初出库数量
from(
select code,name,Spec,unit,OutAmount
from V_material_out_1
where date1 between @dt1 and @dt2
group by code,name,Spec,unit,OutAmount
)V_mayerial_in_1 full join(
select code,name,Spec,unit,期初出库数量=sum(OutAmount)
from V_material_out_1
where date1<@dt1
group by code,name,Spec,unit
)V_material_out_1 on V_mayerial_in_1.code=V_material_out_1.code
)V_material_out_1 on V_mayerial_in_1.code=V_material_out_1.code
set nocount off
GO
劳驾大伙帮我看看?
视图1:V_mayerial_in_1 字段有date1(入库年月),code,name,spec,unit,InAmount(入库数量),Inmoney(所对应的入库金额)
视图2:V_material_out_1 字段有date1(出库年月),code,name,spec,unit,OutAmount(出库数量)
通过存储过程来计算显示:所对应的出库金额。
计算公式如下:
1,本月结存数量=上月结存数量+本月入库数量-本月出库数量
2,本月结存金额=((上月结存金额+本月入库金额)/(上月结存数量+本月入库数量))*本月结存数量
3,本月出库金额=上月结存金额+本月入库金额-本月结存金额
具体存储过程如下:
CREATE PROCEDURE p_total @Yearmonth Varchar(8)
AS
set nocount on
declare @dt1 datetime,@dt2 datetime
select @dt1=@Yearmonth +'01',@dt2=dateadd(month,1,@dt1)-1
select Date1 =@Yearmonth
,code=isnull(V_mayerial_in_1.code,V_material_out_1.code)
,name=isnull(V_mayerial_in_1.name,V_material_out_1.name)
,Spec=isnull(V_mayerial_in_1.Spec,V_material_out_1.Spec)
,Unit=isnull(V_mayerial_in_1.Unit,V_material_out_1.Unit)
,上月结存数量=isnull(期初入库数量,0)-isnull(期初出库数量,0)
,上月结存金额=isnull(期初收入金额,0){问题就在这里,其实这里:上月结存金额=isnull(期初收入金额,0)-isnull(期初出库金额,0)
这里的期初出库金额---也就是上个月的出库金额,问题就是存储过程如何自己调用自己的上个月的出库金额!!!}
,本月结存金额=(isnull(期初收入金额,0)+isnull(Inmoney,0))/(isnull(期初入库数量,0)-isnull(期初出库数量,0)+isnull(InAmount,0))*(isnull(期初入库数量,0)-isnull(期初出库数量,0))
,本月入库数量=isnull(InAmount,0),本月出库=isnull(OutAmount,0)
,本月收入金额=isnull(Inmoney,0)
,本月结存数量=isnull(期初入库数量,0)-isnull(期初出库数量,0)
+isnull(InAmount,0)-isnull(OutAmount,0)
,本月发出金额=isnull(期初收入金额,0)+isnull(Inmoney,0)-(isnull(期初收入金额,0)+isnull(Inmoney,0))/(isnull(期初入库数量,0)-isnull(期初出库数量,0)+isnull(InAmount,0))*(isnull(期初入库数量,0)-isnull(期初出库数量,0))
from(
select code=isnull(V_mayerial_in_1.code,V_material_out_1.code)
,name=isnull(V_mayerial_in_1.name,V_material_out_1.name)
,Spec=isnull(V_mayerial_in_1.Spec,V_material_out_1.Spec)
,Unit=isnull(V_mayerial_in_1.Unit,V_material_out_1.Unit)
,InAmount,期初入库数量,Inmoney,期初收入金额
from(
select code,name,Spec,unit,InAmount,Inmoney
from V_mayerial_in_1
where Date1 between @dt1 and @dt2
group by code,name,Spec,unit,InAmount,Inmoney
)V_mayerial_in_1 full join(
select code,name,Spec,unit,期初入库数量=sum(InAmount),期初收入金额=sum(Inmoney)
from V_mayerial_in_1
where date1<@dt1
group by code,name,Spec,unit
)V_material_out_1 on V_mayerial_in_1.code=V_material_out_1.code
)V_mayerial_in_1 full join(
select code=isnull(V_mayerial_in_1.code,V_material_out_1.code)
,name=isnull(V_mayerial_in_1.name,V_material_out_1.name)
,Spec=isnull(V_mayerial_in_1.Spec,V_material_out_1.Spec)
,Unit=isnull(V_mayerial_in_1.Unit,V_material_out_1.Unit)
,OutAmount,期初出库数量
from(
select code,name,Spec,unit,OutAmount
from V_material_out_1
where date1 between @dt1 and @dt2
group by code,name,Spec,unit,OutAmount
)V_mayerial_in_1 full join(
select code,name,Spec,unit,期初出库数量=sum(OutAmount)
from V_material_out_1
where date1<@dt1
group by code,name,Spec,unit
)V_material_out_1 on V_mayerial_in_1.code=V_material_out_1.code
)V_material_out_1 on V_mayerial_in_1.code=V_material_out_1.code
set nocount off
GO
劳驾大伙帮我看看?
,上月结存金额=isnull(期初收入金额,0){问题就在这里,其实这里:上月结存金额=isnull(期初收入金额,0)-isnull(期初出库金额,0)
这里的期初出库金额---也就是上个月的出库金额,问题就是存储过程如何自己调用自己的上个月的出库金额!!!}
create procedure pr_test @month as int
as
if @month=0
select 0 as value
else
begin
create table #temp(value int)
insert #temp select value from pr_test @month-1 //--我得查一下是不是这样写
select a.value-b.value as value from mytable a,#temp b
end
return
你的方法有道理。我去看看。
INSERT .. EXEC..过程不能嵌套,你在看看能不能用其它方式解决这个问题create procedure pr_test @number as int
as
if @number<=0
select 0 as value
else begin
set @number=@number-1
create table #temp (value int)
INSERT #temp EXEC pr_test @number
select cast(rand()*100 as int) -value as value from #temp
end
return