SQL - Subtracting a Depleting Value from Rows

SQL - Subtracting a depleting value from rows

Left as an exercise to the OP: Figuring out the correct results given the sample data and summarizing the results of the following query:

-- Create some test data.
declare @Pooled_Lots as table ( Id int, Pool int, Lot int, Quantity int );
insert into @Pooled_Lots ( Id, Pool, Lot, Quantity ) values
  ( 1, 1, 1, 5 ), ( 2, 1, 2, 10 ), ( 3, 1, 3, 4 ),
  ( 4, 2, 1, 7 ),
  ( 5, 3, 1, 1 ), ( 6, 3, 2, 5 );
declare @Pool_Consumption as table ( Id int, Pool int, QuantityConsumed int );
insert into @Pool_Consumption ( Id, Pool, QuantityConsumed ) values
  ( 1, 1, 17 ), ( 2, 2, 8 ), ( 3, 3, 10 );

select * from @Pooled_Lots order by Pool, Lot;
select * from @Pool_Consumption order by Pool;

with Amos as (
  -- Start with Lot 1 for each Pool.
  select PL.Pool, PL.Lot, PL.Quantity, PC.QuantityConsumed,
    case
      when PC.QuantityConsumed is NULL then PL.Quantity
      when PL.Quantity >= PC.QuantityConsumed then PL.Quantity - PC.QuantityConsumed
      when PL.Quantity < PC.QuantityConsumed then 0
      end as RunningQuantity,
    case
      when PC.QuantityConsumed is NULL then 0
      when PL.Quantity >= PC.QuantityConsumed then 0
      when PL.Quantity < PC.QuantityConsumed then PC.QuantityConsumed - PL.Quantity
      end as RemainingDemand
    from @Pooled_Lots as PL left outer join
      @Pool_Consumption as PC on PC.Pool = PL.Pool
    where Lot = 1
  union all
  -- Add the next Lot for each Pool.
  select PL.Pool, PL.Lot, PL.Quantity, CTE.QuantityConsumed,
    case
      when CTE.RunningQuantity + PL.Quantity >= CTE.RemainingDemand then CTE.RunningQuantity + PL.Quantity - CTE.RemainingDemand
      when CTE.RunningQuantity + PL.Quantity < CTE.RemainingDemand then 0
      end,
    case
      when CTE.RunningQuantity + PL.Quantity >= CTE.RemainingDemand then 0
      when CTE.RunningQuantity + PL.Quantity < CTE.RemainingDemand then CTE.RemainingDemand - CTE.RunningQuantity - PL.Quantity
      end
    from Amos as CTE inner join
      @Pooled_Lots as PL on PL.Pool = CTE.Pool and PL.Lot = CTE.Lot + 1
  )
select *,
  case
    when Lot = ( select max( Lot ) from @Pooled_Lots where Pool = Amos.Pool ) then RunningQuantity - RemainingDemand
    else NULL end as SurplusOrDeficit
  from Amos
  order by Pool, Lot;

Subtract variable from column while variable has remaining value

You can do this with running sums:

with cte as(select *, sum(col1) over(order by rn) as s from t)
select rn, col1, 0 as col2 from cte where s <= 1000 
union all
select * from (select top 1 rn, col1, s - 1000 as col2 from cte where s > 1000 order by rn)t
union all
select * from (select rn, col1, col1 as col2 from cte where s > 1000 order by rn offset 1 row)t

This is the version that is born in my head without checking. I can not check right now, but I think it should work and it is setbased.

The idea is to make a running sum:

rownumber  col1   s        
1          5000   5000    
2          1000   6000   
3          10000  16000    
4          12000  28000
5          300    28300      
6          35000  63300  

Now you are selecting all rows where s <= 50000. This will give you rn{1,2,3,4,5}. Note you make col2 all zeros. In second union you are selecting first row where s > 50000. This will give you rn{6}. Note col2 is 63300 - 50000 = 13300. Now if you have additional rows then you are selecting those in third union except of first row which we already selected in second union.

How can I subtract a previous row in sql?

Assuming you have an ordering column -- say id -- then you can do the following in SQL Server 2012:

select col,
       col - coalesce(lag(col) over (order by id), 0) as diff
from t;

In earlier versions of SQL Server, you can do almost the same thing using a correlated subquery:

select col,
       col - isnull((select top 1 col
                     from t t2
                     where t2.id < t.id
                     order by id desc
                    ), 0)
from t

This uses isnull() instead of coalesce() because of a "bug" in SQL Server that evaluates the first argument twice when using coalesce().

You can also do this with row_number():

with cte as (
      select col, row_number() over (order by id) as seqnum
      from t
     )
select t.col, t.col - coalesce(tprev.col, 0) as diff
from cte t left outer join
     cte tprev
     on t.seqnum = tprev.seqnum + 1;

All of these assume that you have some column for specifying the ordering. It might be an id, or a creation date or something else. SQL tables are inherently unordered, so there is no such thing as a "previous row" without a column specifying the ordering.

Performing a running subtraction in T-SQL

The main idea

Think of your dollar amounts as intervals on the number line. Place your Invoices and Payments in correct order on the line adjacent to each other.

Invoices, Receiver/Sender ID=1

|----100---|----100---|--------200--------|----------->
0         100        200                 400
ID    1          2              5

Payments, Receiver/Sender ID=1

|-50-|-45|-------------------------------------------->
0   50  95
ID 1   2

Put both sets of intervals together (intersect them):

|----|---|-|----------|-------------------|----------->
0   50  95 100       200                 400

Now you have intervals:

From    To    InvoiceID    PaymentID
------------------------------------
   0    50            1            1
  50    95            1            2
  95   100            1
 100   200            2
 200   400            5

---

Invoices, Receiver/Sender ID=2

|----100---|----100---|------------------------------->
0         100        200                 
ID    3          4

Payments, Receiver/Sender ID=2

|--95----|-----105----|------------------------------->
0       95           200
ID   3          4

Put both sets of intervals together (intersect them):

|--------|-|----------|------------------------------->
0       95 100       200                 

Now you have intervals:

From    To    InvoiceID    PaymentID
------------------------------------
   0    95            3            3
  95   100            3            4
 100   200            4            4

---

For each of these intervals there can be at most one invoice and at most one payment (there can be none as well). Find which invoice and payment correspond to each of these intervals and you've got a mapping between your invoices and payments. Sum up all Payment intervals for each Invoice and you'll know whether invoice was paid in full or partially.

---

Building initial list of intervals separately for Invoices and Payments is done by running total.

SUM(Amount) OVER (PARTITION BY ReceiverId ORDER BY Priority 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS InvoiceInterval

SUM(Amount) OVER (PARTITION BY SenderId ORDER BY PaymentID
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PaymentInterval

Intersecting these two sets is a simple UNION.

For each interval find a corresponding Invoice and Payment. One simple way to do it is subquery in OUTER APPLY.

Let's put all this together.

Sample data

DECLARE @Invoice TABLE
(
    [InvoiceId]     [int] NOT NULL,
    [ReceiverId]    [int] NOT NULL,
    [Amount]        [numeric](19, 2) NOT NULL,
    [Priority]      [int] NOT NULL
);

DECLARE @Payment TABLE
(
    [PaymentId] [int] NOT NULL,
    [SenderId]  [int] NOT NULL,
    [Amount]    [numeric](19, 2) NOT NULL
);

INSERT INTO @Invoice(InvoiceId,ReceiverId,Amount,Priority) VALUES
(1, 1, 100.00, 1),
(2, 1, 100.00, 2),
(3, 2, 100.00, 1),
(4, 2, 100.00, 2),
(5, 1, 200.00, 3);

INSERT INTO @Payment(PaymentId, SenderId, Amount) VALUES
(1, 1,  50.00),
(2, 1,  45.00),
(3, 2,  95.00),
(4, 2, 105.00);

Query

WITH
CTE_InvoiceIntervals
AS
(
    SELECT
        I.InvoiceId
        ,I.ReceiverId AS ClientID
        ,I.Priority
        ,SUM(I.Amount) OVER (PARTITION BY I.ReceiverId ORDER BY I.Priority 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS InvoiceInterval
    FROM @Invoice AS I
)
,CTE_PaymentIntervals
AS
(
    SELECT
        P.PaymentId
        ,P.SenderId AS ClientID
        ,P.PaymentId AS Priority
        ,SUM(P.Amount) OVER (PARTITION BY P.SenderId ORDER BY P.PaymentID
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PaymentInterval
    FROM @Payment AS P
)
,CTE_AllIntervals
AS
(
    SELECT
        ClientID
        ,InvoiceInterval AS Interval
    FROM CTE_InvoiceIntervals

    UNION

    SELECT
        ClientID
        ,PaymentInterval AS Interval
    FROM CTE_PaymentIntervals
)
SELECT *
FROM
    CTE_AllIntervals
    OUTER APPLY
    (
        SELECT TOP(1) CTE_InvoiceIntervals.InvoiceId
        FROM CTE_InvoiceIntervals
        WHERE
            CTE_InvoiceIntervals.ClientID = CTE_AllIntervals.ClientID
            AND CTE_InvoiceIntervals.InvoiceInterval >= CTE_AllIntervals.Interval
        ORDER BY
            CTE_InvoiceIntervals.InvoiceInterval
    ) AS A_Invoices
    OUTER APPLY
    (
        SELECT TOP(1) CTE_PaymentIntervals.PaymentId
        FROM CTE_PaymentIntervals
        WHERE
            CTE_PaymentIntervals.ClientID = CTE_AllIntervals.ClientID
            AND CTE_PaymentIntervals.PaymentInterval >= CTE_AllIntervals.Interval
        ORDER BY
            CTE_PaymentIntervals.PaymentInterval
    ) AS A_Payments
ORDER BY
    ClientID
    ,Interval;

Result

+----------+----------+-----------+-----------+
| ClientID | Interval | InvoiceId | PaymentId |
+----------+----------+-----------+-----------+
|        1 | 50.00    |         1 | 1         |
|        1 | 95.00    |         1 | 2         |
|        1 | 100.00   |         1 | NULL      |
|        1 | 200.00   |         2 | NULL      |
|        1 | 400.00   |         5 | NULL      |
|        2 | 95.00    |         3 | 3         |
|        2 | 100.00   |         3 | 4         |
|        2 | 200.00   |         4 | 4         |
+----------+----------+-----------+-----------+

---

Related Topics