T-SQL distribute a value between rows without a cursor

Question

I have a simple live-case scenario on the SQL Server 2014. I have a table with orders and their quanities:

declare @qty_to_distribute int = 50

create table orders (id int, priority int, qty int)

insert into orders (id, priority, qty) values
(1, 1, 10),
(2, 2, 30),
(3, 3, 20),
(4, 4, 5)

I have a value, for example 50, which i have to distribute between orders, based on their priority Iiterating through the orders

• if the amount left is greater then quanity of the current order, the order will be realized, and the @qty_to_distribute reduced by the qty of the order,
• if not - the order is cancelled.

Looking at the sample data - the orders 1, 2 and 4 will be realized and #3 cancelled.

Is there a simple, effective and non-cursor solution, preferably based on window functions?

This looks promising, but can't figure out where to put the conditions:

select id,
sum(qty) OVER (ORDER BY priority asc ROWS UNBOUNDED PRECEDING) as qtysum
from orders

id  qtysum
1   10
2   40
3   60      *
4   65      *

http://sqlfiddle.com/#!6/05fa2/2/0

Answer 1

Try this:

create table o (id int, priority int, qty int)

insert into o (id, priority, qty) values
(1, 1, 10),
(2, 2, 30),
(3, 3, 20),
(4, 4, 5),
(5, 5, 1),
(6, 6, 10)

with cte1 as(select *, row_number() over(order by priority) as rn from o),
cte2 as(
  select top 1 id, priority, qty, rn,
  case when qty > 50 then 1 else 0 end as ToBeCanceled
  from cte1 order by rn
    Union all
  select cte1.id, cte1.priority, case when cte2.qty + cte1.qty > 50 then cte2.qty
  else cte2.qty + cte1.qty end as qty,
  cte1.rn,
  case when cte2.qty + cte1.qty > 50 then 1 else 0 end as ToBeCanceled 
  from cte1
  join cte2 on cte1.rn = cte2.rn + 1)
select id, priority, ToBeCanceled from cte2
option(maxrecursion 0)

Output:

id  ToBeCanceled
1   0
2   0
3   1
4   0
5   0
6   1

Fiddle http://sqlfiddle.com/#!6/87ac9/19

Answer 2

UPDATED thanks to Giorgi Nakeuri

WITH Intermediate AS (
  SELECT 
      Id, 
      ROW_NUMBER() OVER (ORDER BY Priority) as Priority, 
      qty                
  FROM Orders),
Result AS
(
    SELECT 
        Id, 
        Priority,
        CASE when qty < 50 THEN 0 ELSE qty END as Residual,
        CASE when qty < 50 THEN 50 - qty ELSE 50 END as Remaining   
    FROM Intermediate
    WHERE Priority = 1
    UNION ALL  
    SELECT  
         ORD.Id, 
         ORD.Priority, 
         CASE when ORD.qty < Result.Remaining THEN 0 ELSE qty END as Residual,
         CASE when ORD.qty < Result.Remaining THEN Result.Remaining - ORD.qty ELSE Result.Remaining END as Remaining
    FROM Intermediate ORD INNER JOIN Result ON ORD.Priority = Result.Priority + 1
  )
  select Id, Priority, Residual, Remaining from result

http://sqlfiddle.com/#!6/2ac98f/2