how to calculate stock balance in sql server 2005

Question

Hi

I have two relational tables for Goods Receiving like tblReceipt & tblReceiptDetail. In tblReceipt has three columns like ReceiptID,Date,SupplierName and in tblReceiptDetail has four columns like ReceiptID,ItemID,ItemName,Qty

and two relational tables  for issuing goods like tblIssued and tblIssuedDetail. In tblIssued has three columns like IssuedID,Date,Department and in tblIssuedDetail has four columns IssuedID,ItemID,ItemName,Qty.

Now I want to calculate stock balance each item  wise separtely according to the following formula

OpeningStock+Receipt-IssuedStock

in order to known about how much a particular item is received or issued and how much balance is remain in sql management studio 2005

please help me

qayyum

Answer 1

Hi haqayyum,

In order to get faster response I would move this question to

TransactSQL forum

http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads

Thanks,

Ahmed

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.

Answer 2

sir i follow the above link but i can't find where you move my question

Answer 3

I mean you need to re-post your question here to get fast answer

http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.

Answer 4

Hello Ahmed,

The best way is to post the agreement and a moderator will move the thread ASAP towards the good forum. The move is one of the most important and usual tasks of the moderators ( even if some moderators prefer to write : post on this forum and often mark their post as the good answer , a very bad behaviour... ).

Moreover, a move avoids to lose every post already existing in the thread.

Have a nice day

PS : the original poster having created a new thread,

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a900053a-60e2-47de-b940-b7a05e45da88/#a900053a-60e2-47de-b940-b7a05e45da88

i change the type of this thread to general discussion to avoid to let a thread dying....

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 5

hi,

you can perhaps resolve your problem "ordering" your receival and issuing rows by itemId, date and receival/issuingId, thus assigning a generated "order entry" via the ROW_NUMBER (OVER PARTITION) windowing function available in SQL Server 2005.. the first "step" is to unify receival and issuing rowset, so that all movement will be included, and, as indicated, "numbering" the result of the UNION operation...

this way, each issue/receival for each particular item will be "numbered" from 1 to N (where N is the number of "movements" for the particular order) and you can later aggregate the qty for stock determination at each particular date or sequential movement... a common table expression can help here out limiting the subquery definitions

SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.tblReceipt (id int, actdate date);
CREATE TABLE dbo.tblReceiptDetail (rid int, itmid int, qty int);

CREATE TABLE dbo.tblIssued (id int, actdate date);
CREATE TABLE dbo.tblIssuedDetail (rid int, itmid int, qty int);

INSERT INTO dbo.tblReceipt 
	VALUES (1, GETDATE());
INSERT INTO dbo.tblReceiptDetail
	VALUES (1, 1, 2), (1, 2, 2), (1, 3, 3);	

INSERT INTO dbo.tblReceipt 
	VALUES (2, GETDATE() +1);
INSERT INTO dbo.tblReceiptDetail
	VALUES (2, 1, 2), (2, 3, 2);	

INSERT INTO dbo.tblIssued
	VALUES (1, GETDATE() +1);
INSERT INTO dbo.tblIssuedDetail
	VALUES (1, 1, 1);	

INSERT INTO dbo.tblIssued
	VALUES (2, GETDATE() +2);
INSERT INTO dbo.tblIssuedDetail
	VALUES (2, 1, 1), (2, 2, 1);	

GO
WITH rr AS (
	SELECT 1 AS received, rd.rid, rd.itmid, rd.qty
		, r.actdate
		FROM dbo.tblReceiptDetail rd
		JOIN dbo.tblReceipt r ON r.id = rd.rid
	UNION
	SELECT -1 AS received, id.rid, id.itmid, id.qty
		, i.actdate
		FROM dbo.tblIssuedDetail id
		JOIN dbo.tblIssued i ON i.id = id.rid
	),
	comb AS (
	SELECT rr.itmid, rr.actdate, rr.qty * rr.received AS qty
		, ROW_NUMBER() OVER (PARTITION BY rr.itmid ORDER BY rr.actdate, rr.rid, rr.received DESC) AS [r]
		FROM rr
	)	
	SELECT c.*
		, c.qty + ISNULL((SELECT SUM(qty) FROM comb c2 WHERE c2.itmid = c.itmid AND c2.r < c.r), 0) AS [stock]
		FROM comb c	
		ORDER BY c.itmid, c.r;
GO
DROP TABLE dbo.tblReceiptDetail, dbo.tblReceipt, dbo.tblIssuedDetail, dbo.tblIssued;

the aggregation uses an inline subquery to "sum" all the movement quantity "older" than the current one and adding it to the current movement qty, so that the current balance is defined..

regards

---

http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/

Answer 6

please check out this link.

http://boardreader.com/thread/How_to_create_stored_procedure_to_calcul_oy3hwX5ajtt.html