使用SQL的Fifo库存 [英] Fifo Inventory with SQL

问题描述

我需要对第一个表格进行调整，因为存在负发行点，并且我需要净表将负发行点视为第一次发行的借方.例如:

I need one adaptation for the first table because there are negative issues points and I need the net table considerating the negatives points as debit of the first time of issue. E.g:

FechaEmi Cuenta PtosEmi PtosCan
30/06/2015  1   100     0
31/07/2015  1   120     0
31/08/2015  1   130     0
31/08/2015  1   0       55
30/09/2015  1   50      0
31/10/2015  1   30      0
30/11/2015  1   70      0
31/12/2015  1   95      0
31/01/2016  1   50      0
29/02/2016  1   0       74
31/03/2016  1   50      0
30/04/2016  1   15      0
30/06/2015  2   20      0
31/07/2015  2   30      0
31/08/2015  2   40      0
30/09/2015  2   350     0
30/06/2015  3   150     0
31/07/2015  3   120     0
31/08/2015  3   0       56
31/08/2015  3   220     0
30/06/2015  4   70      0
31/07/2015  4   134     0
31/08/2015  4   12      0
30/06/2015  5   97      0
31/07/2015  5   130     0
31/08/2015  5   15      0
30/09/2015  5   135     0
31/10/2015  5   20      0
30/11/2015  5   140     0
31/12/2015  5   25      0
31/01/2016  5   145     0
29/02/2016  5   0       25

其中:

FechaEmi =日期；

FechaEmi= Date;

Cuenta = ID;

Cuenta=ID;

PtosEmi =发放分数；

PtosEmi=Issues points;

PtosCan =取消的点

PtosCan=Canceled points

我想要这张桌子

FechaEmi    Cuenta  PtosEmi
30/06/2015  1   0
31/07/2015  1   91
31/08/2015  1   130
30/09/2015  1   50
31/10/2015  1   30
30/11/2015  1   70
31/12/2015  1   95
31/01/2016  1   50
31/03/2016  1   50
30/04/2016  1   15
30/06/2015  2   20
31/07/2015  2   30
31/08/2015  2   40
30/09/2015  2   350
30/06/2015  3   94
31/07/2015  3   120
31/08/2015  3   220
30/06/2015  4   70
31/07/2015  4   134
31/08/2015  4   12
30/06/2015  5   72
31/07/2015  5   130
31/08/2015  5   15
30/09/2015  5   135
31/10/2015  5   20
30/11/2015  5   140
31/12/2015  5   25
31/01/2016  5   145

我有这个代码.问题在于，对于没有问题点的日期中借记的点不执行任何操作.您如何建议我更改该查询?谢谢！

I have this code. The problem is that doesn't do anything with the points that were debited in a date that there are not issues point. How can you recommend me change that query? Thanks!

with cte as( 
select Fechaemi, Cuenta,PtosEmi,PtosCan
,row_number() over (partition by Fechaemi,Cuenta order by Fechaemi,Cuenta) as rank 
from emision) 
select a.Fechaemi, a.Cuenta,a.PtosEmi - coalesce(b.PtosCan, 0) stock 
from cte a 
left join cte b on 
a.FechaEmi= b.FechaEmi and a.Cuenta = b.Cuenta and a.rank = b.rank - 1 
where a.PtosEmi - coalesce(b.PtosCan, 0) > 0 order by a.cuenta asc, a.fechaemi asc

推荐答案

SQL FIDDLE DEMO

with totalPay as( 
    SELECT Cuenta, SUM(PtosCan) TotalPayment
    FROM emision
    GROUP BY Cuenta
),
totalDebt as (
    SELECT FechaEmi, Cuenta, (SELECT SUM(PtosEmi) 
                              FROM emision e2 
                              WHERE e2.FechaEmi <= e.FechaEmi
                              AND   e2.Cuenta = e.Cuenta                          
                             ) AS TotalDebt
    FROM emision e
    WHERE   e.PtosEmi <> 0
)
select  
    e.FechaEmi, 
    e.Cuenta, 
    e.PtosEmi, 
    td.TotalDebt, 
    tp.TotalPayment,
    CASE 
        WHEN td.TotalDebt < tp.TotalPayment THEN 0
        WHEN td.TotalDebt - tp.TotalPayment > PtosEmi THEN PtosEmi
        ELSE td.TotalDebt - tp.TotalPayment
    END Remaining
FROM 
    totalDebt  td inner join 
    totalPay tp on td.Cuenta = tp.Cuenta inner join
    emision e on td.FechaEmi = e.FechaEmi AND td.Cuenta = e.Cuenta
WHERE 
    e.PtosEmi <> 0

这篇关于使用SQL的Fifo库存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！