27,579
社区成员
发帖
与我相关
我的任务
分享
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'