How to get opening balance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to get opening balance

Author

Topic

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-07 : 09:41:39

I am working on Stocks. and i have 3 tables:stock,stockIn,stockOut.
Stock(name,description,unitprice,stockInHand,stockSold)
StockIn(received,date,details,receiptno)
StockOut(sold,date,receiptno,salesamount,discount)

Now i have to get the opening balance and closing balance for a particular day( for each item in the stock table).
How should i do tht??? Somebody help me out please.......

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 09:51:55

See this thread. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67373
You can make use of similar technique to do it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 05:07:10

If the link i posted does not helps you, please post some sample data for your tables and the result that you want.

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-08 : 05:40:47

Ok.. this is the data:

stock:
1,stk1,desc1,100,10,10
2,stk2,desc2,150,5,5
3,stk3,desc3,200,0,20

stockIn:
1,stk1,2006-05-06,det1,101
1,stk2,2006-06-06,det2,102
1,stk3,2006-07-06,det3,103

stockOut:
1,stk1,2006-05-06,det1,101,0,1000
1,stk2,2006-06-06,det2,102,0,750
1,stk3,2006-07-06,det3,103,0,4000

The output that i want is:

(Id, name, opening stock, stockIn, StockOut, Closing stock, Unit price, Sales Amount)
1, stk1, 15, 20, 10, 25, 100, 1000
2, stk2, 15, 10, 5, 20, 150, 750
3, stk3, 15, 20, 20, 15, 200, 4000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 05:54:05

bettyatolive,

the sample data you posted does not seems to match the table structure in your first post.
Could you kindly re-post as per this link http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-08 : 06:14:18

Ok..I am sorry for the inconvience.. This is the data:

Create table stock(Id,name,description,unitprice,stockInHand,stockSold)
Create table stockIn(Id_fk,received,date,details,receiptno)
Create table stockOut(Id_fk,sold,date,receiptno,salesamount,discount)

Insert into stock(Id,name,description,unitprice,stockInHand,stockSold)
select 1,'stk1','desc1',100,10,10 union all
select 2,'stk2','desc2',150,5,5 union all
select 3,'stk3','desc3',200,0,20 union all

Insert into stockIn(Id_fk,received,date,details,receiptno)
select 1,'stk1','2006-05-06','det1',101 union all
select 2,'stk2','2006-06-06','det2',102 union all
select 3,'stk3','2006-07-06','det3',103 union all

Insert into stockOut(Id_fk,sold,date,receiptno,salesamount,discount)
select 1,'stk1','2006-05-06','det1',101,0,1000 union all
select 2,'stk2','2006-06-06','det2',102,0,750 union all
select 3,'stk3','2006-07-06','det3',103,0,4000 union all

The output that i want is:

Id, name, openingStock, stockIn, StockOut, Closing stock, Unit price, Sales Amount
------------------------------------------------------------------------------------
1, stk1, 15, 20, 10, 25, 100, 1000
2, stk2, 15, 10, 5, 20, 150, 750
3, stk3, 15, 20, 20, 15, 200, 4000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 09:38:47

quote:
The output that i want is:

Id, name, openingStock, stockIn, StockOut, Closing stock, Unit price, Sales Amount
------------------------------------------------------------------------------------
1, stk1, 15, 20, 10, 25, 100, 1000
2, stk2, 15, 10, 5, 20, 150, 750
3, stk3, 15, 20, 20, 15, 200, 4000

Can you explain how to get these value ?
- openingstock
- stockin
- stockout
- closing stock, etc

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-08 : 09:51:34

I need to know how to get the values of opening and closing stock...
stockin i get from received field of stockIn table and
i get stockout from sold field of stockOut table..

I'm sorry i gave you the data wrong... Here is the new one...

Insert into stockIn(Id_fk,received,date,details,receiptno)
select 1,20,'2006-05-06','det1',101 union all
select 2,10,'2006-06-06','det2',102 union all
select 3,20,'2006-07-06','det3',103 union all

Insert into stockOut(Id_fk,sold,date,details,receiptno,discount,salesamount)
select 1,10,'2006-05-06','det1',101,0,1000 union all
select 2,5,'2006-06-06','det2',102,0,750 union all
select 3,20,'2006-07-06','det3',103,0,4000 union all

The remaining data is same...

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-08 : 10:05:23

Opening stock is the stock tht is present with them for tht particular item on the previous day.
And tht becomes the opening stosk for tht day..
And closing stock = OpeningStock + stockIn - stockOut;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 10:10:05

bettyatolive,

Can you explain how to get the opening balance ?

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-08 : 10:17:44

khtan,

That is the question i have !!!

I need to know the previous days last transactions for every item.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 10:19:50

i mean how to calculate to get the opening balance as 15 for all 3 stk1,2,3. What is the formula ?

"I need to know the previous days last transactions for every item."
So the result that you want (that you posted) is for which date ?

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-08 : 10:26:50

I just gave you those assumed those values... U asked for sample data ..so i just gave you those figures..
I think it this way...
OpeningStock for today= (sum of all the received stock for the previous day) - ( sum of all the sold stock for the previous day)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 10:30:54

So this result that you wanted is as of which date ? 2006-05-06 ? 2006-06-06 ? 2006-07-06 ?

quote:
Id, name, openingStock, stockIn, StockOut, Closing stock, Unit price, Sales Amount
------------------------------------------------------------------------------------
1, stk1, 15, 20, 10, 25, 100, 1000
2, stk2, 15, 10, 5, 20, 150, 750
3, stk3, 15, 20, 20, 15, 200, 4000

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-08 : 10:33:30

this result is today's.. ie 2006-08-06...

All the items are received n sold on different dates ...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 10:36:32

quote:
Originally posted by bettyatolive

this result is today's.. ie 2006-08-06...

All the items are received n sold on different dates ...

today is 2006-06-08. Don't tell me your date is in YYYY-DD-MM ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources