使用SQL的Fifo库存

本文介绍了使用SQL的Fifo库存的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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