Find out old and new quantity on bases of PO number .

Question

1.00/5 (1 vote)

See more:

I have a table in which there is column of quantity. So some time quantity change of particular item number on bases of PO number . Now my problem is I have to find out what is new quantity and old quantity of particular item code .

What I have tried:

Try to find out old and new quantity .

Posted 10-Aug-20 22:25pm

Archana K

Updated 14-Aug-20 0:59am

Add a Solution

Comments

Garth J Lancaster 11-Aug-20 4:37am

You don't provide a lot of information .. what else is in this table ?, what are you going to use this information for ??

if you have a product table then sure, it might have a quantity, but does it also make sense to have new and old quantities ? ..

Maybe you need to think about an audit or transaction log, such that, if a PO uses x items, a timestamped audit/log entry is created for each item, showing the new and old quantity - you can then use this for analysis

If you can provide more schema info, better solutions may be forthcoming - Use Improve question to update your question.

Archana K 11-Aug-20 5:39am

There is a table tbl_product that contains quantity column so from this quantity column I have to find new and old quantity .

1 solution

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)

W∴ Balboos, *GHB* · Answer 1 · 2020-08-14T00:59:00

It's no problem at all if you're tables properly designed - this solution can be used generally. This, of course, assumes they're your table and not some fixed body for a homework assignment.

Each entry should have a unique identifier column (IDENTITY field ins MS SQL). Add to this another column, like "item_reference". When first order for the item is entered then you set the itme_reference value equal to the IDENTITY value. If you modify the order then you use the IDENTIFIER of the original record to set the value for "item_reference". Now you can build a history for each item if the amount is changed.

Take into account the multiple records when "fulfilling" the order that you use the latest entry in quantity.

This is serialization (one way of doing it). Another option is to create an archive file for the previous item records. This latter makes it easier to compose the final order (only the live records are in the table) but requires joining perhaps multiple tables.

To make a lot of this automatic, look into learning SQL TRIGGERS