22,210
社区成员
发帖
与我相关
我的任务
分享
if object_id('[TB]') is not null drop table [TB]
create table TB
(
FDate varchar(10),
FBilltype varchar(50),
FItem varchar(50),
FStock varchar(50),
FBegQty Decimal(28,10),
FInQty Decimal(28,10),
FOutQty Decimal(28,10),
FEndQty Decimal(28,10)
)
insert [TB]
select '2009-01-01','外购入库','A物料','A仓库',10,1,0,0 union all
select '2009-02-01','其他入库','A物料','A仓库',0,3,0,0 union all
select '2009-03-01','销售出库','A物料','A仓库',0,0,2,0 union all
select '2009-01-02','外购入库','B物料','B仓库',20,4,0,0 union all
select '2009-02-02','其他入库','B物料','B仓库',0,2,0,0 union all
select '2009-03-02','销售出库','B物料','B仓库',0,0,4,0
select 日期=FDate,
单据类型=FBilltype,
物料=FItem,
仓库=FStock,
期初数量=isnull((select sum(FBegQty+FInQty-FOutQty) from TB where t.FItem=FItem and t.FDate>FDate),FBegQty),
收入数量=FInQty,
发出数量=FOutQty,
结存数量=isnull((select sum(FBegQty+FInQty-FOutQty) from TB where t.FItem=FItem and t.FDate>FDate),FBegQty)+FInQty-FOutQty
from TB t
/*
日期 单据类型 物料 仓库 期初数量 收入数量 发出数量 结存数量
---------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
2009-01-01 外购入库 A物料 A仓库 10.0000000000 1.0000000000 0.0000000000 11.0000000000
2009-02-01 其他入库 A物料 A仓库 11.0000000000 3.0000000000 0.0000000000 14.0000000000
2009-03-01 销售出库 A物料 A仓库 14.0000000000 0.0000000000 2.0000000000 12.0000000000
2009-01-02 外购入库 B物料 B仓库 20.0000000000 4.0000000000 0.0000000000 24.0000000000
2009-02-02 其他入库 B物料 B仓库 24.0000000000 2.0000000000 0.0000000000 26.0000000000
2009-03-02 销售出库 B物料 B仓库 26.0000000000 0.0000000000 4.0000000000 22.0000000000
(6 行受影响)
*/
drop table TB
[code=SQL]update t
set
t.FBegQty=(select top 1 FBegQty+FInQty-FEndQty from tt where FDate<t.FDate),
t.FEndQty=t.FBegQty+t.FInQty-t.FEndQty
from
tt t
[/code]update tt
set FEndQty=FBegQty+FInQty-FEndQty
select * from tt
select 日期,单据类型,物料,仓库,期初数量,收入数量,发出数量,结存数量=期初数量+收入数量-发出数量
from 表