求物料收发明细表计算每日结存数量的SQL

Question

各位大侠:小弟现在在做一张物料收发明细报表时,遇到了如下难题,恳请赐教. 问题描述如下,
现有如下数据的表：
日期        单据类型   物料   仓库    期初数量  收入数量  发出数量  结存数量
2009-01-01  外购入库   A物料   A仓库    10         1         0        0
2009-02-01  其他入库   A物料   A仓库    0          3         0        0
2009-03-01  销售出库   A物料   A仓库    0          0         2        0
2009-01-02  外购入库   B物料   B仓库    20         4         0        0
2009-02-02  其他入库   B物料   B仓库    0          2         0        0    
2009-03-02  销售出库   B物料   B仓库    0          0         4        0
...
如何写出求出每种物料每日的期初数量和结存数量的SQL？能得到如下的结果？   
日期        单据类型   物料    仓库 期初数量  收入数量  发出数量  结存数量
2009-01-01  外购入库   A物料    A仓库  10          1         0        11
2009-02-01  其他入库   A物料    A仓库  11          3         0        14
2009-03-01  销售出库   A物料    A仓库  14          0         2        12
2009-01-02  外购入库   B物料    B仓库  20          4         0        24
2009-02-02  其他入库   B物料    B仓库  24          2         0        26    
2009-03-02  销售出库   B物料    B仓库  26          0         4        22 
...
表结构：
create table tt
(
   FDate datetime,
   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) 
)

Answer 1

select 日期,单据类型,物料,仓库,期初数量,收入数量,发出数量,结存数量=期初数量+收入数量-发出数量
from 表

Answer 2

update tt
set FEndQty=FBegQty+FInQty-FEndQtyselect * from tt

Answer 3

to jianshun:
这样可以算出结存数量,但是如何计算出每日的期初数量呢?这个表可能有很多种物料,表按物料进行了分组,如果是一个物料还好,问题是有多个物料的情况,就不知如何处理了。

Answer 4

没有环境测试，试试
[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]

Answer 5

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,0select  日期=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

Answer 6

谢谢各位的热心帮助！按jiangshun的方法，问题已解决！