我有以下表格結構
TransactionDate Item RecQty IssueQty1-jun-2011 A 10 02-jun-2011 A 15 02011 年 6 月 3 日 A 20 02011 年 6 月 4 日 A 0 202011 年 6 月 4 日 A 0 20我想要特定日期的結果，即 2-jun-2011
Item Opening RecQty IssueQty BalanceQty10 15 0 252011 年 1 月 4 日
Item Opening RecQty IssueQty BalanceQty45 0 40 5我打算至少假設 SQL Server 2005.將來，請使用您需要支持的最低版本指定/標記問題.
>
創建表#x(交易日期 SMALLDATETIME,項目 CHAR(1), RecQty INT, IssueQty INT);插入 #x 選擇 '20110601','A',10,0UNION ALL SELECT '20110602','A',15,0UNION ALL SELECT '20110603','A',20,0UNION ALL SELECT '20110604','A',0,20UNION ALL SELECT '20110604','A',0,20;聲明 @StartDate SMALLDATETIME = '20110601',@Date SMALLDATETIME = '20110602';WITH x(Item, prevR, prevI, curR, curI) AS(選擇物品，SUM(CASE WHEN TransactionDate < @Date THEN RecQty ELSE 0 END),SUM(CASE WHEN TransactionDate < @Date THEN IssueQty ELSE 0 END),SUM(CASE WHEN TransactionDate = @Date THEN RecQty ELSE 0 END),SUM(CASE WHEN TransactionDate = @Date THEN IssueQty ELSE 0 END)FROM #x WHERE TransactionDate BETWEEN @StartDate 和 @Date按項目分組)選擇物品，開場 = prevR - prevI,RecQty = curR,IssueQty = curI,BalanceQty = (prevR - prevI) + (curR - curI)從 x;刪除表#x;I have the following table strucature
TransactionDate Item RecQty IssueQty
1-jun-2011 A 10 0
2-jun-2011 A 15 0
3-jun-2011 A 20 0
4-jun-2011 A 0 20
4-jun-2011 A 0 20
And I want the result on specific Date i.e 2-jun-2011
Item Opening RecQty IssueQty BalanceQty
A 10 15 0 25
And on 4-Jan-2011
Item Opening RecQty IssueQty BalanceQty
A 45 0 40 5
I'm going to assume at least SQL Server 2005. In the future, please specify/tag the question with the minimum version you need to support.
CREATE TABLE #x
(
TransactionDate SMALLDATETIME,
Item CHAR(1), RecQty INT, IssueQty INT
);
INSERT #x SELECT '20110601','A',10,0
UNION ALL SELECT '20110602','A',15,0
UNION ALL SELECT '20110603','A',20,0
UNION ALL SELECT '20110604','A',0,20
UNION ALL SELECT '20110604','A',0,20;
DECLARE @StartDate SMALLDATETIME = '20110601',
@Date SMALLDATETIME = '20110602';
WITH x(Item, prevR, prevI, curR, curI) AS
(
SELECT
Item,
SUM(CASE WHEN TransactionDate < @Date THEN RecQty ELSE 0 END),
SUM(CASE WHEN TransactionDate < @Date THEN IssueQty ELSE 0 END),
SUM(CASE WHEN TransactionDate = @Date THEN RecQty ELSE 0 END),
SUM(CASE WHEN TransactionDate = @Date THEN IssueQty ELSE 0 END)
FROM #x WHERE TransactionDate BETWEEN @StartDate AND @Date
GROUP BY Item
)
SELECT
Item,
Opening = prevR - prevI,
RecQty = curR,
IssueQty = curI,
BalanceQty = (prevR - prevI) + (curR - curI)
FROM x;
DROP TABLE #x;
這篇關于如何計算期初和期末數量余額 SQL Server的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！