Sliding window functions in SQL Server, advanced calculation

Question

I have a problem that it's very easy to be solved in C# code for example, but I have no idea how to write in a SQL query.

Here is the situation: let's say I have a table with 3 columns (ID, Date, Amount), and here is some data:

ID  Date         Amount
-----------------------
1   01.01.2016    -500
2   01.02.2016    1000
3   01.03.2016    -200
4   01.04.2016     300
5   01.05.2016     500
6   01.06.2016    1000
7   01.07.2016    -100
8   01.08.2016     200

The result I want to get from the table is this (ID, Amount .... Order By Date):

ID  Amount
-----------------------
2    300
4    300
5    500
6    900
8    200

The idea is to distribute the amounts into installments, but the thing is when negative amount comes into play you need to remove amount from the last installment. I don't know how clear I am, so here is an example:

Let's say I have 3 Invoices with amounts 500, 200, -300.

If i start distribute these Invoices, first i distribute the amount 500, then 200. But when i come to the third one -300, then i need to remove from the last Invoice. In other workds 200 - 300 = -100, so the amount from second Invoice will disappear, but there are still -100 that needs to be substracted from first Invoice. So 500 - 100 = 400. The result i need is dataset with one row (first invoice with amount 400)

Another example when the first invoice is with negative amount (-500, 300, 500). In this case, the first (-500) invoice will make the second disappear and another 200 will be substracted from the third. So the result will be: Third Invoice with amount 300.

This is something like Stack implementation in programming language, but i need to make it with sliding-window functions in SQL Server.

If anyone have any idea, please share.

Thanks.

Why is the `amount` of `2` equal to `300`. According to your explanation it should be `500` (= `-500+100`), or maybe not? — Giorgos Betsos, Nov 16 '17 at 09:28
The real amount of 2 is 1000, But the actual should be 1000(2)-500(1)-200(3) = 300 The negative amounts needs to be substracted from the amounts before them, but in this case the first amount is negative, and it must be substracted from the first positive that comes after it — Stefan Taseski, Nov 16 '17 at 09:30
I believe that the second row should be `4|300` and the third row should be `5|500` — Radim Bača, Nov 16 '17 at 09:42
it's still unclear on how you define a group (meaning how we know to group the items here?). That involves defining the sliding-window as well as the window frame so that we can calculate the values as desired. — King King, Nov 16 '17 at 09:58
looks like the `ID` values are also translated as well? That's the point making your problem not easy to understand at first. — King King, Nov 16 '17 at 10:00
@RadimBača you are right, i had some mistake in the result. It should be fine now. — Stefan Taseski, Nov 16 '17 at 10:12
https://stackoverflow.com/questions/47329339/sql-implementation-with-sliding-window-functions-or-recursive-ctes — Stefan Taseski, Nov 16 '17 at 12:10

Sergey Menshov · Accepted Answer · 2017-11-16T11:10:32.630

I solved it using TSQL. But I think what this task also can solve using recursive CTE. I used ID for finding a prev or next row.

-- create and fill test table
CREATE TABLE Invoices(
  ID int,
  [Date] date,
  Amount float
)

INSERT Invoices(ID,Date,Amount) VALUES
(1,'20160101', -500),
(2,'20160201', 1000),
(3,'20160301', -200),
(4,'20160401',  300),
(5,'20160501',  500),
(6,'20160601', 1000),
(7,'20160701', -100),
(8,'20160801',  200)

My solution

-- copy all the data into temp table
SELECT *
INTO #Invoices
FROM Invoices

DECLARE
  @nID int,
  @nAmount float,
  @pID int

-- run infinity loop
WHILE 1=1
BEGIN

  -- set all the variables to NULL
  SET @nID=NULL
  SET @nAmount=NULL
  SET @pID=NULL

  -- get data from the last negative row
  SELECT
    @nID=ID,
    @nAmount=Amount
  FROM
    (
      SELECT TOP 1 *
      FROM #Invoices
      WHERE Amount<0
      ORDER BY ID DESC
    ) q

  -- get prev positive row
  SELECT @pID=ID
  FROM
    (
      SELECT TOP 1 *
      FROM #Invoices
      WHERE ID<@nID
        AND Amount>0
      ORDER BY ID DESC
    ) q

  IF(@pID IS NULL)
  BEGIN
    -- get next positive row
    SELECT @pID=ID
    FROM
      (
        SELECT TOP 1 *
        FROM #Invoices
        WHERE ID>@nID
          AND Amount>0
        ORDER BY ID
      ) q
  END

  -- exit from loop
  IF(@pID IS NULL) BREAK

  -- substract amount from positive row
  UPDATE #Invoices
  SET
    Amount+=@nAmount
  WHERE ID=@pID

  -- delete used negative row
  DELETE #Invoices
  WHERE ID=@nID

END

-- show result
SELECT *
FROM #Invoices

DROP TABLE #Invoices

I think it will work, but in the requirements i have cycles are not an option. Just sliding-functions or Recursive CTEs. And other thing, this is an example for one client let's say. I need to make partitioning so i can group invoices by client etc etc ... so I have no idea how — Stefan Taseski, Nov 16 '17 at 11:19
@GordonLinoff you are right, i will ask more specific questions next time. Thanks for the input. — Stefan Taseski, Nov 16 '17 at 11:54
https://stackoverflow.com/questions/47329339/sql-implementation-with-sliding-window-functions-or-recursive-ctes — Stefan Taseski, Nov 16 '17 at 12:10

Sliding window functions in SQL Server, advanced calculation

1 Answers1