求货物库存台账表计算结存数量和结存金额sql语句

[duanyanhua0521]

我现在在做一张货物库存台账表，遇到了难题，请各位大侠帮帮忙指教一下：
我现在有如下表：
日期 单据类型 物料 仓库 收入数量 发出数量 rdsflag
2009-01-01 外购入库 A物料 A仓库 1 0 1
2009-02-01 其他入库 A物料 A仓库 3 0 1
2009-03-01 销售出库 A物料 A仓库 0 2 -1
2009-01-02 外购入库 B物料 B仓库 4 0 1
2009-02-02 其他入库 B物料 B仓 2 0 1
2009-03-02 销售出库 B物料 B仓 0 4 -1
如何写出每种货物当日的结存数量的SQL？
能得到如下的结果？
日期 单据类型 物料 仓库 收入数量 发出数量 结存数量
10
2009-01-01 外购入库 A物料 A仓库 1 0 11
2009-02-01 其他入库 A物料 A仓库 3 0 14
2009-03-01 销售出库 A物料 A仓库 0 2 12
2009-01-02 外购入库 B物料 B仓库 4 0 16
2009-02-02 其他入库 B物料 B仓库 2 0 18
2009-03-02 销售出库 B物料 B仓库 0 4 14


表结构：
create table tt
(
StockDate datetime, //业务日期
SrcType varchar(50), //单据类型
GoodName varchar(50), //货物名称
Branch varchar(50), //仓库
NUM Decimal(28,10), //出入库/数量
rdsflag int
)




存储过程
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure cx_getAccount_RD @StockDate datetime, @enddate datetime
as
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
--库存台帐表
declare @num decimal( 18, 2) --期初件数
declare @TotalPrice decimal( 18, 2) --期初金额

SELECT @num=SUM( rdsFlag * Num1)
FROM tt
WHERE StockDate <@StockDate

SELECT NULL AS 业务日期, '' as 单据类型, '' AS 仓库,
'' as 货物名称,
0 AS 入库件数,
0 AS 出库件数,
ISNULL(@num, 0) AS 结存件数,

UNION ALL

SELECT StockDate,SrcType,Branch,GoodsName,
SIGN(rdsflag + 1) * Num AS 入库件数,
SIGN(rdsflag - 1) * - 1 * Num1 AS 出库件数,
这里不知道怎么写 as 结存件数
FROM tt
WHERE StockDate >=@StockDate AND StockDate <=@enddate
go

求大神指教

[Tiger_Zhao]

求期初件数时不分物料的吗？

要用临时表，按天循环插入。
简单点的方式就是直接以该天有发生的(单据类型,物料,仓库)为条件，把到当天为止的明细直接合计起来。

[还在加载中灬]

CREATE procedure cx_getAccount_RD
	@StockDate datetime, @enddate datetime
AS
BEGIN
	SET NOCOUNT ON
	SET ANSI_WARNINGS OFF

	;WITH ttCTE AS(
		SELECT *
			,ROW_NUMBER()OVER(PARTITION BY GoodName,Branch ORDER BY StockDate)RN
		FROM tt
		WHERE StockDate>=@StockDate AND StockDate<=@enddate
	)
	,CTE AS(
		SELECT
			T1.StockDate--业务日期
			,T1.SrcType--单据类型
			,T1.GoodName--货物名称
			,T1.Branch--仓库
			,CASE WHEN T1.rdsFlag>0 THEN T1.NUM ELSE 0 END InNUM--收入数量
			,CASE WHEN T1.rdsFlag<0 THEN T1.NUM ELSE 0 END OutNUM--发出数量
			,ISNULL(T2.NUM1,0)+T1.NUM*T1.rdsFlag SUMNUM--结存数量
			,T1.RN
		FROM
			ttCTE T1
				LEFT JOIN(
					SELECT GoodName,Branch
						,SUM(rdsFlag*NUM)NUM1
					FROM tt
					WHERE StockDate<@StockDate
					GROUP BY GoodName,Branch
				)T2 ON T1.GoodName=T2.GoodName AND T1.Branch=T2.Branch
		WHERE T1.RN=1
		UNION ALL
		SELECT
			T1.StockDate
			,T1.SrcType
			,T1.GoodName
			,T1.Branch
			,CASE WHEN T1.rdsFlag>0 THEN T1.NUM ELSE 0 END InNUM
			,CASE WHEN T1.rdsFlag<0 THEN T1.NUM ELSE 0 END OutNUM
			,T2.SUMNUM+T1.NUM*T1.rdsFlag
			,T1.RN
		FROM ttCTE T1
			JOIN CTE T2 ON T1.GoodName=T2.GoodName AND T1.Branch=T2.Branch
				AND T1.RN=T2.RN+1
	)
	SELECT * FROM CTE
	ORDER BY GoodName,Branch,RN
	END
GO
----
EXEC cx_getAccount_RD '2009-01-01','2009-03-02'

[duanyanhua0521]

解决了： 存储过程 SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER procedure cx_getAccount_RD @StockDate datetime, @enddate datetime as SET NOCOUNT ON SET ANSI_WARNINGS OFF --库存台帐表 declare @num decimal( 18, 2) --期初件数 declare @TotalPrice decimal( 18, 2) --期初金额 SELECT @num=SUM( rdsFlag * Num1) FROM tt WHERE StockDate <@StockDate SELECT NULL AS 业务日期, '' as 单据类型, '' AS 仓库, '' as 货物名称, 0 AS 入库件数, 0 AS 出库件数, ISNULL(@num, 0) AS 结存件数, UNION ALL SELECT StockDate,SrcType,Branch,GoodsName, SIGN(rdsflag + 1) * Num AS 入库件数, SIGN(rdsflag - 1) * - 1 * Num1 AS 出库件数, 0.00 as 结存件数 FROM tt WHERE StockDate >=@StockDate AND StockDate <=@enddate go 是在程序里结算的： for (int i = 1; i < source.Rows.Count; i++) { source.Rows[i]["结存数量"] = float.Parse(source.Rows[i - 1]["结存数量"].ToString()) + float.Parse(source.Rows[i]["入库数量"].ToString()) - float.Parse(source.Rows[i]["出库数量"].ToString()); source.Rows[i]["结存金额"] = float.Parse(source.Rows[i - 1]["结存金额"].ToString()) + float.Parse(source.Rows[i]["入库金额"].ToString()) - float.Parse(source.Rows[i]["出库金额"].ToString()); }

[Tiger_Zhao]

期初件数不分物料，开头就不对啊！