按月统计期初、期末数据

Question

测试数据不全啊，从测试数据看不出怎么得出那个结果，你先解释下：Opening    IN     IN_Retrun   OUT   OUT_Return 这些分别是什么？

Answer 1

;WITH CTE AS(
SELECT Item,CONVERT(VARCHAR(7),Date,120)Date
,ISNULL(SUM(CASE WHEN Flag>0 AND Quantity>=0 THEN Quantity END),0)[IN]
,ABS(ISNULL(SUM(CASE WHEN Flag>0 AND Quantity<0 THEN Quantity END),0))[IN_Retrun]
,ISNULL(SUM(CASE WHEN Flag<=0 AND Quantity>=0 THEN Quantity END),0)[OUT]
,ABS(ISNULL(SUM(CASE WHEN Flag<=0 AND Quantity<0 THEN Quantity END),0))[OUT_Return]
FROM TB
GROUP BY Item,CONVERT(VARCHAR(7),Date,120)
)
,CTE2 AS(
SELECT T1.Item,T1.[Date],T1.[IN],T1.[IN_Retrun],T1.[OUT],T1.[OUT_Return]
,SUM(CASE WHEN T1.Date=T2.Date THEN 0
ELSE T2.[IN]-T2.[IN_Retrun]-T2.[OUT]+T2.[OUT_Return]
END)Opening
,SUM(T2.[IN]-T2.[IN_Retrun]-T2.[OUT]+T2.[OUT_Return])Balance
FROM CTE T1
JOIN CTE T2 ON T1.Item=T2.Item AND T1.Date>=T2.Date
GROUP BY T1.Item,T1.[Date],T1.[IN],T1.[IN_Retrun],T1.[OUT],T1.[OUT_Return]
)
SELECT T1.Item,T1.Date,T1.Opening+ISNULL(T2.Balance,0) Opening
,T1.[IN],T1.[IN_Retrun],T1.[OUT],T1.[OUT_Return]
,T1.Balance+ISNULL(T2.Balance,0) Balance
FROM CTE2 T1
LEFT JOIN Stocks T2 ON T1.Item=T2.Item
WHERE T1.Date>='2005-01'--按月时间条件放在这
ORDER BY Item,Date

Answer 2

Item(品名)    Date（年-月）       Opening（本月期初）    IN（本月入库）     IN_Retrun（本月入库退回）   OUT（本月出库）   OUT_Return（本月出库退回）   Balance（本月期末/本月库存） =============请使用SQL 2000

Answer 3

期望的结果应该是如下：
/*--结果
Item    Date       Opening    IN     IN_Retrun   OUT   OUT_Return   Balance 
---------- ---------------- -------------- ----------- ---------------- ----------- ------------------- ----------- 
aa     2005-02      225      200     10          0       5            420
bb     2005-02      0        195     15          65      20           135
cc     2005-02      100       0      0           0       0            100
aa     2005-03      420       0      10          0       0            410
--*/

Answer 4

SELECT Item,CONVERT(VARCHAR(7),Date,120)Date
,MIN(Opening)Opening
,ISNULL(SUM(CASE WHEN Flag>0 AND Quantity>=0 THEN Quantity END),0)[IN]
,ABS(ISNULL(SUM(CASE WHEN Flag>0 AND Quantity<0 THEN Quantity END),0))[IN_Retrun]
,ISNULL(SUM(CASE WHEN Flag<=0 AND Quantity>=0 THEN Quantity END),0)[OUT]
,ABS(ISNULL(SUM(CASE WHEN Flag<=0 AND Quantity<0 THEN Quantity END),0))[OUT_Return]
,MAX(Balance)Balance
FROM (
SELECT T1.ID,T1.Item,T1.Date,T1.Flag,T1.Quantity
,ISNULL(T3.Balance,0)+SUM(CASE WHEN T1.ID=T2.ID THEN 0 WHEN T2.Flag>0 THEN T2.Quantity ELSE -T2.Quantity END)Opening
,ISNULL(T3.Balance,0)+SUM(CASE WHEN T2.Flag>0 THEN T2.Quantity ELSE -T2.Quantity END)Balance
FROM TB T1
JOIN TB T2 ON T1.Item=T2.Item AND(T1.Date>T2.Date OR(T1.Date=T2.Date AND T1.ID>=T2.ID))
LEFT JOIN Stocks T3 ON T1.Item=T3.Item
GROUP BY T1.ID,T1.Item,T1.Date,T1.Flag,T1.Quantity,T3.Balance
)T
--WHERE Date>='2005-01-01'时间条件放在此处
GROUP BY Item,CONVERT(VARCHAR(7),Date,120)
ORDER BY Item,Date