Iterate through list and fill column
-
hi,
i need some help with iterating through a list and checking its running total against a parameter. see example below:
as you can see above there are currently 10 items in the basket already, and the max number of items is 20.
the orders come in and the first one will fit as there are 8. the running total in basket is now 18. that means i have space for 2 items.
orders 2, 3, 4 will not fit.
order 5 can as that order has 1 item. running total is 19. 1 item left.
and so on.
Until it hits 20, the remaining is zero. Therefore i can use this list and filter out WHERE [Fit?] = 1.
SELECT *
INTO #TestTable
FROM (VALUES
(1, 8)
,(2,8)
,(3,7)
,(4,5)
,(5,1)
,(6,3)
,(7,1)
,(8,2)
,(9,1)
,(10,1)
)v(Order,Items)
; -
Hi
This is how I would iterate through the listselect * , cast( 0 as bit) as fit , cast(0 as int) as runningcheck , ROW_NUMBER() over (order by orders) as rn into #testtable2
from #testtable
declare @maxorder as int = 20
declare @runningcheck as int = 10
declare @i int = 1
declare @rn_max int = (Select max(rn) from #testtable2)while @i <= @rn_max
begin
set @runningcheck = @runningcheck + (select items from #testtable2 where rn = @i )begin
if @runningcheck <= @maxorder
update t
set fit = 1
,runningcheck = @runningcheck
from #testtable2 t where rn = @iset @runningcheck = (select max(runningcheck) from #testtable2)
set @i = @i + 1
end
end -
By defining a recursive cte this problem can be solved.
First define an anchor row for the cte where orders=1.
I go on to define 3 new columns
1. "balance" to tell me how much is left if there is a fitting criteria, which is 10-items>0,
2. "used" to tell how much has been used basically 10 + items if there is a fittment
3. "fitting" flag to indicate if there is a successful match (ie the balance fits with the items)After that the second part of the cte ie the portion after UNION ALL is createdby linking it with the next order
ie (ON a.orders1 + 1 = b.orders)
WITH base_table
AS (SELECT *
FROM (VALUES (1,8),
(2,8),
(3,7),
(4,5),
(5,1),
(6,3),
(7,1),
(8,2),
(9,1),
(10,1) )v (orders, items)),
cte(orders1, items1, balance1, used1, fitting1)
AS (SELECT a.orders AS orders, /* This is the starting row for the recursive cte*/
a.items AS items,
Cast(( CASE
WHEN 10 - a.items > 0 THEN 10 - a.items
ELSE 10
END ) AS INT) AS balance, /* Balance is defined as 10-the items if the items is less than 10 else it is kept as is*/
Cast(( CASE
WHEN 10 - a.items > 0 THEN 10 + a.items
ELSE 10
END ) AS INT) AS used, /* The used field tells how much has been matched after 10 elecments */
Cast(( CASE
WHEN 10 - a.items > 0 THEN 'Y'
ELSE 'N'
END ) AS VARCHAR(1)) AS fitting /* This flag indicates if there the value is under 10 and a successful match was obtained*/
FROM base_table a
WHERE a.orders = 1 /* We start with the first order*/
UNION ALL
SELECT b.orders,
b.items,
Cast(( CASE
WHEN a.balance1 - b.items >= 0 THEN
a.balance1 - b.items
ELSE a.balance1
END ) AS INT),
Cast(( CASE
WHEN a.balance1 - b.items >= 0 THEN a.used1 + b.items
ELSE a.used1
END ) AS INT),
Cast(( CASE
WHEN a.balance1 - b.items >= 0 THEN 'Y'
ELSE 'N'
END ) AS VARCHAR(1))
FROM cte a
JOIN base_table b
ON a.orders1 + 1 = b.orders /* Get the next order from the previous one(anchorOrder + 1 = nextOrder)*/
)
SELECT *
FROM cte -
Choose WHERE fitting='Y' to get the records which have been matched successfully
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply