我现在有两个表,一个表入库,一个表出库,把这个期间的进出明细统计出来,但这段时间前的期初数,怎样统计出来材料:AB001 名称:铁管 单位:米 日期段: 20090620-2009820 期初:100
日期 进/出 数量 结存数量
20090621 进 10 110
20090621 出 50 60
20090811 出 10 50明细好做,但期初怎样做,可以根据用户自定义找到期初,如果这样数据量大了会不会算出来好慢
日期 进/出 数量 结存数量
20090621 进 10 110
20090621 出 50 60
20090811 出 10 50明细好做,但期初怎样做,可以根据用户自定义找到期初,如果这样数据量大了会不会算出来好慢
if object_id('t1')is not null
drop table t1
go
create table t1(日期 datetime, [进/出] varchar(20), 数量 int, 结存数量 int )
insert t1 select
'20090621' , '进' , 10 , 0 union all select
'20090621' , '出' , 50 , 0 union all select
'20090811' , '出', 10 ,0declare @n int
set @n=100update t1
set @n=case when [进/出]='进' then @n+数量+结存数量 else @n-数量+结存数量 end ,结存数量=@nselect *from t1
日期 进/出 数量 结存数量
----------------------- -------------------- ----------- -----------
2009-06-21 00:00:00.000 进 10 110
2009-06-21 00:00:00.000 出 50 60
2009-08-11 00:00:00.000 出 10 50(3 行受影响)
if object_id('t1')is not null
drop table t1
go
create table t1(日期 datetime, [进/出] varchar(20), 数量 int, 结存数量 int )
insert t1 select
'20090621' , '进' , 10 , 0 union all select
'20090621' , '出' , 50 , 0 union all select
'20090811' , '出', 10 ,0declare @n int
set @n=100update t1
set @n=case when [进/出]='进' then @n+数量+结存数量 else @n-数量+结存数量 end ,结存数量=@n
where 日期 between '20090620' and '20090820'select *from t1
日期 进/出 数量 结存数量
----------------------- -------------------- ----------- -----------
2009-06-21 00:00:00.000 进 10 110
2009-06-21 00:00:00.000 出 50 60
2009-08-11 00:00:00.000 出 10 50(3 行受影响)
declare @c table(id int, num int, date datetime)insert into @r
select 1,10,'2009-06-21'
insert into @c
select 1,50,'2009-06-21'
union all select 1,10,'2009-06-21'declare @x int
declare @t table(date datetime,rc bit,num int,sx int)
set @x = 100 --初期
declare cr cursor for select * from @r where id =1
declare cc cursor for select * from @c where id =1
declare @id int
declare @num int
declare @date datetime
open cr
fetch next from cr into @id,@num,@date
while @@fetch_status = 0
begin
insert into @t values(@date,1,@num,@x+@num)
set @x = @x +@num
fetch next from cr into @id,@num,@date
end
close cr
DEALLOCATE cropen cc
fetch next from cc into @id,@num,@date
while @@fetch_status = 0
begin
insert into @t values(@date,0,@num,@x-@num)
set @x = @x -@num
fetch next from cc into @id,@num,@date
end
close cc
DEALLOCATE cc
--最后查询@t
select * from @t/*
date rc num sx
------------------------------------------------------ ---- ----------- -----------
2009-06-21 00:00:00.000 1 10 110
2009-06-21 00:00:00.000 0 50 60
2009-06-21 00:00:00.000 0 10 50
*/
这个跟海关用的,原料进出明细帐一样的。
1、先建立一个临时表,料号,日期,上期数量,本期入,本期出,本期余
2、查询出日期范围前的所有结余数据,放在本期余中。尽量想办法让它在每种材料中显示在第一列。
3、查询出时间范围内的每天的进出数量到数据库中。每种材料,每天只有一笔记录。
4、然后,自己写一个存储过程,计算临时表,把同料号的,上一笔的结余,更新为下一笔的期初。
5、最后对临时表显示查询。
--附上我4部的存储过程,因为我这个是海关系统,所有存在工厂帐与海关帐两栏,你作一下修改了。
--用於執行把數據表的單位進行累加
CREATE PROC sp_SumQTY
@TableName varchar(30)
As
--定義變量
DECLARE @strSQL varchar(8000) --查詢語句
DECLARE @PrimaryField varchar(30) --主鍵欄位名稱
DECLARE @SumSql1 varchar(200) --合計數據欄
DECLARE @SumSql2 varchar(200) --合計數據欄
DECLARE @SumField1 varchar(30) --累加欄位
DECLARE @SumField2 varchar(30) --累加欄位
--設定對應值
SET @PrimaryField='List_No'
SET @SumField1='Stock_amt1'
SET @SumField2='Stock_amt2'
SET @SumSql1='(IsNull(inbar_amt1,0)-IsNull(outbar_amt1,0))'
SET @SumSql2='(IsNull(inbar_amt2,0)-IsNull(outbar_amt2,0))'
--定義累加時使用的變量
DECLARE @ID varchar(30)
DECLARE @Mtr_No varchar(30)
DECLARE @Num1 numeric(18,6)
DECLARE @Num2 numeric(18,6)
DECLARE @TolNum1 numeric(18,6)
DECLARE @TolNum2 numeric(18,6)
DECLARE @Old_Mtr_No varchar(30) --設置不用顯示記錄行
SET NOCOUNT ON
--初始化舊材料編號為空字符
SET @Old_Mtr_No=''
--定義數據表游標
SET @strSQL='DECLARE SumColumnOfTableName SCROLL CURSOR FOR '+
'SELECT mtr_no,'+@PrimaryField+','+
@SumSql1+' As num1,'+@SumSql2 +' As num2 '+
'FROM '+@TableName +' ' +
'ORDER BY ' +@PrimaryField+' ' +
'FOR READ ONLY '
EXEC (@strSQL)
--打開游標
OPEN SumColumnOfTableName
--取數據
FETCH NEXT FROM SumColumnOfTableName INTO @Mtr_No,@ID,@Num1,@Num2
--判斷是否有記錄
IF @@FETCH_STATUS<>0
BEGIN
--釋放游標
DEALLOCATE SumColumnOfTableName
--重置顯示記錄行
SET NOCOUNT OFF
RETURN
END
--逐條記錄進行累加動作
WHILE @@FETCH_STATUS=0
BEGIN
IF @Old_Mtr_No<>@Mtr_No
BEGIN
SET @TolNum1=0
SET @TolNum2=0
END
SET @TolNum1=@TolNum1+@Num1
SET @TolNum2=@TolNum2+@Num2
SET @strSQL='UPDATE '+@TableName+' '+
'SET '+@SumField1+'='+CONVERT(varchar(30),@TolNum1)+','+
@SumField2+'='+CONVERT(varchar(30),@TolNum2) +
'FROM ' +@TableName +' '+
'WHERE '+@PrimaryField+'='+@ID
EXEC (@strSQL)
--保存上一筆記錄的材料編號
SET @Old_Mtr_No=@Mtr_No
FETCH NEXT FROM SumColumnOfTableName INTO @Mtr_No,@ID,@Num1,@Num2
END
--釋放游標
DEALLOCATE SumColumnOfTableName
--重置顯示記錄行
SET NOCOUNT OFF
RETURN