Stored procedure to calculate the quantity on hand based on sales, purchases and returns

Question

1.00/5 (2 votes)

See more:

Is it advisable to calculate on the fly , the qty available for every item or calculate it every time an item is sold? What is the stored procedure on how to do this if it is calculate don the fly.

Posted 24-Jun-13 15:20pm

RomanRapido

Updated 25-Jun-13 0:09am

CPallini

v3

Add a Solution

Comments

The Doer 24-Jun-13 21:48pm

Lack of Information.
"how to do this if it is calculate don the fly."
What this means??

"What is the stored procedure on how to do this"
Without knowing the table structure how one can help you writing the SP??

RomanRapido 25-Jun-13 4:28am

Here is a link and there is an image for the tables that I have created on sql server.

Amol_B 25-Jun-13 1:15am

Please elaborate

RomanRapido 25-Jun-13 4:19am

Thank you Arun Rajendra for your reply. I have done that before but most suggested that it is right to summarize sales and purchases including returns then subtract and add. My question is that , how about if there are millions of records already on sales and purchases alone? They said it is very quick on sql server using view. I have asked this question so that experts like you can comment and suggest on this. If that is the case then will it be applicable also to store Customers balance as a field? then later on, if there are payments made by the customer then I would subtract it when a payment is saved?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ArunRajendra · Answer 1 · 2013-06-24T17:40:00

No it is not advisable from performomance point of view. If I was to implement I would have gone for updatting the Quantity In Hand field every time by adding or subtacting on very sale or return (if applicabale). This can be better manged by writing the update tigger on sales table which will automatically updated qunatity in hand.

Maciej Los · Answer 2 · 2013-06-24T23:41:00

Solution 2

Yes, it's possible to get updated information about items count, etc. To achive that, you need to use SQLDependency[^] class.

More:
Using SqlDependency for data change events[^]
Using SqlDependency in a Windows Application [^]

Posted 24-Jun-13 23:41pm

Maciej Los