SQL查询来计算基于给定的条件下运行的剩余余额(SQL Query to calculate rem

2019-09-17 04:05发布

站内文章 / 后端开发
33 0 0

我有一个股票的交易表所示： 

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

每个我的交易价格已经关联到它和批号（批号）。 现在我想通过先入先出（FIFO）规则来计算剩余量，这意味着首先在应该率先走出调整。 调整量后的剩余余额要针对每个IN事务同样项目计算，如下所示： 

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

正如我们可以从项ABC上表中看到的，在调整后（125 + 100）OUT数量反对使用FIFO其余为批次71001000为0的量的IN数量（100 + 50 + 75），71001001为25和对于批次71001002是75.从剩余量可被导出的值。

请帮我实现这个使用任何方法（无论是基于游标或CTE或连接，等）在此先感谢您的帮助。

一个StockOverflow的用户提出这样的回答： 

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

这个SQL是有问题，结果接在这里 对于该值不匹配的记录黄色表示。 请帮助解决这个问题。

Answer 1:

我想这应该做的伎俩？ 

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


Answer 2:

你的问题我不太清楚在FIFO逻辑是如何被应用。 我会假设你希望每一位员工IN创纪录的打击下OUT如果存在记录。 要做到这一点，你需要加入表上本身喜欢以下 

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'

结果将显示您第5个记录以下ABC从你的问题。 

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

左加入假设作品，对中的每个记录的StockID总是少了一个数比对应的输出记录。 我个人认为你的数据模型需要改进。

  • OUT记录应该有分配BatchNo或在创纪录的相关的StockID参考
  • 添加时间戳字段顺序排序
  • 添加日期时间字段用于在处理/ OUT存在的在同一天


文章来源: SQL Query to calculate remaining running balances based on a given conditions
标签： tsql sql-server-2005
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~