CODEWITHSUNDEEP
sqlsql-serverdatabasesliding-window
Stefan Taseski
Stefan Taseski

Reputation: 232

Sliding window functions in SQL Server, advanced calculation

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.

Upvotes: 2

Views: 1086

Answers (1)

Sergey Menshov
Sergey Menshov

Reputation: 3906

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

Upvotes: 1

Related Questions