how to set closing stock as opening stock

shantanu 2013-09-29 13:13:06

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 = '20110604';

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; .............................. 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 The above code is working fine,but when i set @StartDate SMALLDATETIME = '20110602', @Date SMALLDATETIME = '20110602'; it does not sum the previous quantity of '20110601' I need the result below when i input SMALLDATETIME = '20110603', @Date SMALLDATETIME = '20110603' Item Opening RecQty IssueQty BalanceQty A 25 20 0 45 please some one help

Autochartist 2013-10-18 09:56:22
To get that result your @StartDate should remain '20110601'.