Click here to Skip to main content
15,304,276 members
Please Sign up or sign in to vote.
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
Updated 3-Feb-14 0:40am
v3
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.

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  
   
v4
Comments
King Fisher 3-Feb-14 7:27am
   
ma 5
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       
   
Comments
StackQ 3-Feb-14 6:42am
   
not aaceptable, coz we already used to do this, running project can't alter for BalQty, used to do it by subtracting.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900