Databases
StackOverflow2013
Postsdbo
POSQL Query to calculate remaining running balances based on a given conditions
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Query to calculate remaining running balances based on a given conditions
    primarykey
    data
    text
    <p>I have a stock transaction table like this:</p> <pre><code>StockID Item TransDate TranType BatchNo Qty Price 10001 ABC 01-Apr-2012 IN 71001000 200 750.0 10002 ABC 02-Apr-2012 OUT 100 10003 ABC 03-Apr-2012 IN 71001001 50 700.0 10004 ABC 04-Apr-2012 IN 71001002 75 800.0 10005 ABC 10-Apr-2012 OUT 125 10006 XYZ 05-Apr-2012 IN 71001003 150 350.0 10007 XYZ 05-Apr-2012 OUT 120 10008 XYZ 15-Apr-2012 OUT 10 10009 XYZ 20-Apr-2012 IN 71001004 90 340.0 10010 PQR 06-Apr-2012 IN 71001005 50 510.0 10011 PQR 15-Apr-2012 IN 71001006 60 505.0 10012 MNO 01-Apr-2012 IN 71001007 76 410.0 10013 MNO 11-Apr-2012 OUT 76 </code></pre> <p>Each of my IN transactions has price associated to it and a batch number (lot number). Now I would like to calculate the remaining quantity by First In First Out (FIFO) rule, meaning the first in should be adjusted with first out. After adjusting the quantities the remaining balances are to be calculated against each IN transaction for the same item as shown below: </p> <pre><code>StockID Item TransDate TranType BatchNo Qty Price RemainingQty 10001 ABC 01-Apr-2012 IN 71001000 200 750.0 0 10002 ABC 02-Apr-2012 OUT 100 10003 ABC 03-Apr-2012 IN 71001001 50 700.0 25 10004 ABC 04-Apr-2012 IN 71001002 75 800.0 75 10005 ABC 10-Apr-2012 OUT 125 10006 XYZ 05-Apr-2012 IN 71001003 150 350.0 20 10007 XYZ 05-Apr-2012 OUT 120 10008 XYZ 15-Apr-2012 OUT 10 10009 XYZ 20-Apr-2012 IN 71001004 90 340.0 90 10010 PQR 06-Apr-2012 IN 71001005 50 510.0 50 10011 PQR 15-Apr-2012 IN 71001006 60 505.0 60 10012 MNO 01-Apr-2012 IN 71001007 76 410.0 0 10013 MNO 11-Apr-2012 OUT 76 </code></pre> <p>As we can see from the above table for item ABC, after adjusting (125 + 100) OUT qty against the IN qty (100 + 50 + 75) using FIFO the quantity remaining for the batch 71001000 is 0, 71001001 is 25 and for batch 71001002 is 75. From the remaining quantity the value can be derived. </p> <p>Please help me to achieve this using any of the methods (either cursor based or CTE or JOINS, etc) Thanks in advance for the help. </p> <p>One of the users of StockOverflow suggested this answer:</p> <pre><code>SELECT 10001 as stockid,'ABC' as item,'01-Apr-2012' as transdate,'IN' as trantype, 71001000 as batchno, 200 as qty, 750.0 as price INTO #sample UNION ALL SELECT 10002 ,'ABC','02-Apr-2012','OUT', NULL ,100,NULL UNION ALL SELECT 10003 ,'ABC','03-Apr-2012','IN', 71001001, 50 , 700.0 UNION ALL SELECT 10004 ,'ABC','04-Apr-2012','IN', 71001002, 75 , 800.0 UNION ALL SELECT 10005 ,'ABC','10-Apr-2012','OUT', NULL ,125,NULL UNION ALL SELECT 10006 ,'XYZ','05-Apr-2012','IN', 71001003, 150 , 350.0 UNION ALL SELECT 10007 ,'XYZ','05-Apr-2012','OUT', NULL , 120 ,NULL UNION ALL SELECT 10008 ,'XYZ','15-Apr-2012','OUT', NULL , 10 ,NULL UNION ALL SELECT 10009 ,'XYZ','20-Apr-2012','IN', 71001004, 90 , 340.0 UNION ALL SELECT 10010 ,'PQR','06-Apr-2012','IN', 71001005, 50 , 510.0 UNION ALL SELECT 10011 ,'PQR','15-Apr-2012','IN', 71001006, 60 , 505.0 UNION ALL SELECT 10012 ,'MNO','01-Apr-2012','IN', 71001007, 76 , 410.0 UNION ALL SELECT 10013 ,'MNO','11-Apr-2012','OUT', NULL ,76 ,NULL ;WITH remaining AS ( SELECT *, CASE WHEN trantype = 'IN' THEN 1 ELSE -1 END * qty AS stock_shift, ROW_NUMBER() OVER(PARTITION BY item ORDER BY transdate) AS row, CASE WHEN trantype = 'OUT' THEN NULL ELSE ROW_NUMBER()OVER(PARTITION BY item, CASE WHEN trantype = 'IN' THEN 0 ELSE 1 END ORDER BY transdate) END AS in_row, SUM(CASE WHEN trantype = 'OUT' THEN qty END) OVER(PARTITION BY item) AS total_out FROM #sample ) ,remaining2 AS ( SELECT r1.item, r1.stockid, MAX(r1.transdate) AS transdate, MAX(r1.trantype) AS trantype, MAX(r1.batchno) AS batchno, MAX(r1.qty) AS qty, MAX(r1.price) AS price, MAX(r1.total_out) AS total_out, MAX(r1.in_row) AS in_row, CASE WHEN MAX(r1.trantype) = 'OUT' THEN NULL WHEN SUM(CASE WHEN r1.trantype = 'IN' THEN r2.qty ELSE 0 END) - MAX(r1.total_out) &lt; 0 THEN SUM(CASE WHEN r1.trantype = 'IN' THEN r2.qty ELSE 0 END) - MAX(r1.total_out) ELSE 0 END AS running_in FROM remaining r1 LEFT OUTER JOIN remaining r2 ON r2.row &lt;= r1.row AND r2.item = r1.item GROUP BY r1.item, r1.stockid ) SELECT r2.item, r2.stockid, MAX(r2.transdate) AS transdate, MAX(r2.trantype) AS trantype, MAX(r2.batchno) AS batchno, MAX(r2.qty) AS qty, MAX(r2.price) AS price, MAX(CASE WHEN r2.trantype = 'OUT' THEN NULL ELSE ISNULL(r2.qty + r3.running_in, 0) END) AS remaining_stock FROM remaining2 r2 LEFT OUTER JOIN remaining2 r3 ON r2.in_row - 1 = r3.in_row AND r2.item = r3.item GROUP BY r2.item, r2.stockid </code></pre> <p>This sql is having a problem and the result is attached here<img src="https://i.stack.imgur.com/Rs2SI.jpg" alt="Query Result"> The records for which the value are not matching are indicated in yellow color. Kindly help to solve the problem.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNagesh
    1. USNagesh
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThe obvious question to ask is why don't you have a BatchNo assigned to the OUT records? It would make things much easier to solve your problem. Your question clearly tells of the relationship, but your table definition does not reflect this.
      singulars
      1. POSQL Query to calculate remaining running balances based on a given conditions
      1. USBrad
    2. COHi @Brad the batch number is just a transaction number created to identify a lot. However, all the lots are physically mixed together and issued. But for accounting purposes the result is required.
      singulars
      1. POSQL Query to calculate remaining running balances based on a given conditions
      1. USNagesh
    3. CO@Nagesh I've modified my original answer below which works for your sample data
      singulars
      1. POSQL Query to calculate remaining running balances based on a given conditions
      1. USDibstar
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload