How to get Right Balance Qty

Question

3.00/5 (2 votes)

See more:

ItemMasterId:- ITEM01
SalesNo:- SALES01
ReqQty:-500

If I am going to ship this ITEM01 in 3 times then how to get bal qty:-

SalesNo	  ITEMNo   CartonCode   ReqQty    ShipQty     BalQty
-------   ------   ----------   ------    --------    ------
SALES01   ITEM01     CC01       500       400          100 ( ReqQty-ShipQty)         
SALES01   ITEM01     CC02       500       80           420 ( ReqQty-ShipQty)         
SALES01   ITEM01     CC03       500       10           490 ( ReqQty-ShipQty)

But the problem is:-
In the First row it's showing BalQty 100 but if we will sum(ShipQty) then balQty must be 10
In the 2nd line I again showing 420 as balqty but already shipped 490
In the 3rd row showing 490 already shipped 490, balqty must be 10

I wanted to get output in below format

XML

<pre>
SalesNo      ITEMNo  CartonCode     ReqQty    ShipQty     BalQty
-------      ------  ------- ----    ------    --------    ------
SALES01      ITEM01   CC01           500       400          10            
SALES01      ITEM01   CC02           500       80           0             
SALES01      ITEM01   CC03           500       10           0      
</pre>

My query is:-
SELECT SalesNo,ItemNo,ReqQty,ShipQty,ReqQty-ShipQty AS BalQty FROM Table

I have also used above query like

SQL

:
SELECT SalesNo,ItemNo,ReqQty,ShipQty,ReqQty-sum(ShipQty) AS BalQty FROM Table

SalesNo      ITEMNo  CartonCode     ReqQty    ShipQty     BalQty
-------      ------  ----------     ------    --------    ------
SALES01      ITEM01  CC01           500       400          10            
SALES01      ITEM01  CC02           500       80           10             
SALES01      ITEM01  CC03           500       10           10

which is still not right .

Please give me suggestion.

Posted 2-Feb-14 23:15pm

StackQ

Updated 3-Feb-14 0:40am

v3

Add a Solution

Comments

joginder-banger 3-Feb-14 5:28am

I think when you shipQty also be update query. I am not clear this but may be help you.

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)

Peter Leow · Answer 1 · 2014-02-03T01:17:00

The BalQty is not needed in the table at all, as its value can be and should be derived dynamically from the transactions, no point storing it as it will be out-of-date upon each new transaction. I suggest keeping the original table without the BalQty column.
You can either derive the BalQty in your code or in sql:

SQL

SELECT salesno, itemno, reqty, SUM(shipqty), reqty - SUM(shipqty) AS balqty
FROM tablename WHERE salesno='sales01' AND itemno='item01'
GROUP BY salesno, itemno, reqty

which will return you the latest qtybal:

SalesNo      ITEMNo      ReqQty    ShipQty     BalQty
-------      ------      ------    --------    ------
SALES01      ITEM01      500       490          10

Basmeh Awad · Answer 2 · 2014-02-02T23:54:00

This will be complicated for you to manipulate such type of transactions it is better to keep the transactions in two diffrent table one should be the header table
which will store the header information like

SQL

SalesNo      ITEMNo      ReqQty    ShipQty     BalQty
-------      ------      ------    --------    ------
SALES01      ITEM01      500       490          10

and the detail table which will store the details of the transactions

SQL

SalesNo	     ITEMNo      ReqQty    ShipQty  
-------      ------      ------    -------- 
SALES01      ITEM01      500       400      
SALES01      ITEM01      500       80       
SALES01      ITEM01      500       10

How to get Right Balance Qty

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0