Fifo Inventory with SQL

Question

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

where:

FechaEmi= Date;

Cuenta=ID;

PtosEmi=Issues points;

PtosCan=Canceled points

I want this table

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

Answer 1

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

Answer 2

Possibly not the most elegant, but explicit way:

WITH 
PtosEmi AS(
  SELECT FechaEmi, cuenta, SUM(PtosEmi) as PtosEmi
  FROM table1
  GROUP BY FechaEmi, cuenta),
PtosCan AS (
  SELECT MIN(FechaEmi) as FechaEmi, cuenta, SUM(PtosCan) as PtosCan 
  FROM table1
  GROUP BY cuenta)
SELECT 
  e.FechaEmi, 
  e.cuenta, 
  e.ptosemi, 
  c.ptoscan,
  e.ptosemi - COALESCE(c.ptoscan, 0) total
FROM 
  PtosEmi e LEFT JOIN 
  PtosCan c ON e.FechaEmi = c.FechaEmi AND e.cuenta = c.cuenta
ORDER BY e.cuenta, e.FechaEmi

This is based on the assumption that you cannot have cancellations before anything was issued for give cuenta.

Also if you have more cancelled items in total compared to what was issued initially, total value will be negative.

http://sqlfiddle.com/#!6/9ac40/11

Update

as you want to reduce cancellations line by line here is updated query:

WITH 
ptosemi AS(
    SELECT FechaEmi, cuenta, 
           PtosEmi as PtosEmi,
           SUM(PtosEmi) OVER (PARTITION BY cuenta ORDER BY FechaEmi) runsum 
    FROM table1),
PtosCan AS (
    SELECT cuenta, SUM(PtosCan) as PtosCan 
    FROM table1
    GROUP BY cuenta)
SELECT 
  e.FechaEmi, 
  e.cuenta, 
  e.ptosemi, 
  e.runsum,
  c.ptoscan,
  CASE 
    WHEN e.runsum <= c.ptoscan
    THEN 0 
    WHEN c.ptoscan BETWEEN e.runsum - e.ptosemi AND e.runsum
    THEN e.runsum - COALESCE(c.ptoscan, 0)
    ELSE e.ptosemi END total
FROM 
 ptosemi e LEFT JOIN 
 PtosCan c ON e.cuenta = c.cuenta
ORDER BY e.cuenta, e.FechaEmi

http://sqlfiddle.com/#!6/8036c2/25