CODEWITHSUNDEEP
t-sqlsql-server-2005
Nagesh
Nagesh

Reputation: 1308

SQL Query to calculate remaining running balances based on a given conditions

I have a stock transaction table like this:

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

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: 

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

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.

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.

One of the users of StockOverflow suggested this answer:

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) < 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 <= 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

This sql is having a problem and the result is attached hereQuery Result The records for which the value are not matching are indicated in yellow color. Kindly help to solve the problem.

Upvotes: 0

Views: 4126

Answers (2)

Dibstar
Dibstar

Reputation: 2364

I think this should do the trick?

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_stock 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
,CASE WHEN trantype = 'IN' THEN NULL ELSE
row_number()OVER (PARTITION BY item,CASE WHEN trantype = 'OUT' THEN 0 ELSE 1 END ORDER BY transdate) END as out_row
,ISNULL(SUM(CASE WHEN trantype = 'OUT' THEN qty END) OVER (PARTITION BY item),0) AS total_out
,ISNULL(SUM(CASE WHEN trantype = 'IN' THEN qty END) OVER (PARTITION BY item),0) AS total_in
FROM #sample
)
,remaining_stock2 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_in) as total_in
,MAX(r1.total_out) as total_out
,SUM(r2.qty) as running_in
FROM remaining_stock r1 
LEFT OUTER JOIN remaining_stock r2 on r2.in_row <= r1.in_row
                    AND r2.item = r1.item       
GROUP BY
r1.item
,r1.stockid 
)
SELECT
item
,stockid
,transdate
,trantype
,batchno
,qty
,price
,CASE WHEN  trantype = 'OUT' THEN NULL
        WHEN total_out >= running_in THEN 0 
        WHEN (running_in - total_out) < qty THEN (running_in - total_out)
        WHEN (running_in - total_out) >= qty THEN qty 
        END as remaining_stocks
FROM remaining_stock2

Upvotes: 1

Brad
Brad

Reputation: 15879

Your question isn't very clear to me on how the FIFO logic is to be applied. I'm going to assume that you want to associate each IN record against the next OUT record if one exists. To achieve this you need to join the table on itself like the following

select
    t1.BatchNo,
    isnull(t1.Qty,0)  as 'IN Qty',
    isnull(t2.Qty,0)  as 'OUT Qty',
    isnull(t1.Qty,0) - isnull(t2.Qty,0) as 'Remaining Qty'
from 
    tbl_test t1
left join tbl_test t2
    on t2.StockID = (t1.StockID + 1)
    and t2.TranType = 'OUT'
where
    t1.TranType = 'IN'

The results will show you the following for the first 5 records for ABC from your question.

BatchNo  | IN Qty | OUT Qty | Remaining Qty
71001000 | 200    | 100     | 100
71001001 | 50     | 0       | 50
71001002 | 75     | 125     | -50

The left join works on the assumption that the StockID for each IN record is always one less number than the associated OUT record. I personally think your data model needs improving.

  • OUT records should have a BatchNo assigned or a reference to the StockID of the associated IN record
  • add a timestamp field for sequential ordering
  • add a DateTime field for handling IN/OUT occuring on same day

Upvotes: 0

Related Questions