Opening & Closing Stock Problem

Question

Opening & Closing Stock Problem

shakir-769418

Old Hand

Points: 309
More actions
January 18, 2009 at 12:58 am

#94453

I am shakir hossain from Bangladesh; I am a beginner of MS SQL 2000 programmer. I need to your help. I try to make a program for store department. I need to make a report daily all Item wise stock report. For example, Opening stock, received qty, total stock qty, sale/issue qty, and closing stock. For more detail, when we want to see first time opening stock comes from initial opening stock, and when we want to see next days stock report then opening stock comes from previous date & previous closing stock.
Date Item Name Opening stock Received qty. Total Qty. Issue/Sale qty. Closing Stock.
14/12/2006 PH meter 10 1 11 5 6
When we want to see 15/12/2006 report opening stock comes from 14/12/2006 closing stock
Date Item Name Opening stock Received qty. Total Qty. Issue/Sale qty. Closing Stock.
15/12/2006 PH meter 6 0 6 1 5
Date Item Name Opening stock Received qty. Total Qty. Issue/Sale qty. Closing Stock.
16/12/2006 PH meter 5 0 5 0 5
I am waiting for your reply.
Thanking You
Shakir Hossain
shakirbd@yahoo.com

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 1

It's not quite clear what you are doing here, or how you want to use this. We also like to see you do some work, show some effort in T-sQL here as we don't do homework questions.

Think MIN(), MAX() with the date and returning the other data. You'll need a GROUP BY clause as well.

Garadin One Orange Chip Points: 29613 More actions · Answer 2

In addition to what Steve has requested, please see the article in my signature on how to provide sample data.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeswanth Jaishanker SSCommitted Points: 1869 More actions · Answer 3

Hi,

if you are creating a stock report like Opening Balance in the first row and daily transactions below and at the end Closing Stock, you can create a functioon which returns a table and call it top as opening balance and last and closing balance and union it with the daily transactions like

select 'By Opening Bal' as header ,f1,f2,f3,dt,ob from dbo.Opening_stockbalance(@dt)

union all

select 'trans',f1,f2,f3,dt,qty from stocktrans where dt = @dt

union all

select 'Clsong,'f1,f2,f3,dt,ob from dbo.Closing_stockbalance(@dt)

hope this will help u ,

Jeswanth

--------------------------------

shakir-769418 Old Hand Points: 309 More actions · Answer 4

shakir-769418

Old Hand

Points: 309

January 19, 2009 at 4:38 am

#929098

Please give me detail solutions.....

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

shakir (1/19/2009)
Please give me detail solutions.....

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Better than all of that, let's see what YOU have tried. We're not consultants that work for free to write other people's code... we're here to help you with the code you've written. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 7

shakir (1/19/2009)
Please give me detail solutions.....

This isn't trying to solve the problem. If you want that, hire someone. We are happy to help, and I gave hints, but you have to do some work as well.

tevers SSC Journeyman Points: 76 More actions · Answer 8

So that's how outsourcing works!! The project goes to XXXX and someone posts it as a question and then sends the answer back. Although I think they have to add a few logic errors and misspellings so the company thinks it was actually done offshore.

Editor's Note: Removed an offensive note in the post. I realize this was in humor, but it was insulting to a group of people.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

tevers (1/19/2009)
So that's how outsourcing works!! The project goes to India and someone posts it as a question and then sends the answer back. Although I think they have to add a few logic errors and misspellings so the company thinks it was actually done offshore.

Heh... pretty much what I was thinking. Welcome aboard, Tevers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

tevers (1/19/2009)
So that's how outsourcing works!! The project goes to India and someone posts it as a question and then sends the answer back.

:hehe: 😀 :w00t:

Thanks. That made my day.

Considering many of the questions I've seen here ans elsewhere, I wouldn't be surprised to find a nugget of truth in that jest.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

tevers (1/19/2009)
So that's how outsourcing works!! The project goes to India and someone posts it as a question and then sends the answer back. Although I think they have to add a few logic errors and misspellings so the company thinks it was actually done offshore.

I guess that makes outsourcing a form of open source development, only the people posting questions get paid while those of us who actually do the work don't.

bhushanvinay SSCrazy Points: 2253 More actions · Answer 12

I am sure that if no outsourcing then the aftershocks will be rightly felt in all parts of the world.

May be a poor lad in this case but Not a bad start for him at this help site.

Welcome aboard :-):P

Regards
Vinay

cs2data Old Hand Points: 344 More actions · Answer 13

I am not really sure what you are asking but, you should have several tables, such as:

Item

ItemInventory

TicketHeader

TicketLine

ItemReceiving

...

Also, many store apps use a concept of "closing" or committing certain transactions (sales, receiving, adjustments, etc.). These commitments may be based on a store, register, or receipt of goods closing based on an event (such as end of day, end of shift, the delivery is here).

Transactions will "hit" your ItemInventory table as determined by business rules.

If you are specifically looking to run a historical stock status report (item qty by date or date by item qty), that is still doable by using something similar to the above. Again, I am not sure what you are asking, the table presented should not be the foundation of your store app.

An answer about the pure mechanics of a writing a query will not benefit you if your concept is flawed.