Increament id if running total for group exceeds value

as you can see below, i want to add an incrementing basket id if running total exceeds 10 for that group.

order 1 , 2 and 3 are for group id 3 but order 3 would exceed the running total of 10, therefore it goes into the next basket id.order 4 is for a new group therefore next basket id.order 5 is for group 7 by running total would exceed 10 therefore assigned next basket id.orders 6 and 7 are for same group but different from previous group therefore will get next basket id.and so on

Well your sample data doesn't seem to match the data you described in your explanation order 5 is actually a new group and doesn't sum out to more than 10 until order 7. But based on your sample data this might work, although it's fairly messy and i'm sure someone can do better. Also what do you expect to happen if you jump more than 10 in between 2 orders, is that two baskets?

Well your sample data doesn't seem to match the data you described explanation order 5 is actually a new group and doesn't sum out to more than 10 until order 7. But based on your sample data this might work, although it's fairly messy and i'm sure someone can do better. Also what do you expect to happen if you jump more than 10 in between 2 orders, is that two baskets?

I will give this a go when I have my laptop.Inhave pre conditioned the order, therefore if an order has >10 items it splits the order into two, or if greater than 30 it's three groups. I have assigned a letter code to that order for example order1_A order1_B and so on. So in that list you won't find an order greater than 10.Sorry about the data being incorrect .I am working with a large dataset and quickly typed that up.

On another note. If a new wave of orders come through, is there a way to add the groupid and current number of items in groupid to a temp table so on the new wave of orders, it checks for the current number of items against if new wave groupid is same as groupid in temp table and brings back the value, so let's say groupid 3 currently has 8 items, the first order in new list happens to be for groupid 3, that order has 3 items therefore current total is now 8 and so on...

select OrderID, Items, GroupID, RunningTotalInGroup, SUM(IncrBasket) OVER(ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as BasketIDfrom ( select OrderID, Items, GroupID, case lag(groupid) over (order by groupid) when GroupID then case when SUM(items) OVER(PARTITION BY groupID ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) >= 10 then 1 else 0 end else 1 end as IncrBasket, SUM(items) OVER(PARTITION BY groupID ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as RunningTotalInGroup from #TestTable) Rslt

You can make use of recursive cte to get the output. The expected results as mentioned by you is slightly incorrect.

with dataas (select row_number() over(order by orderid) as rnk ,* from #testtable ) ,cte(orderid1,items1,groupid1,prev_groupid1,runningtotal1,basketid1,rnk1) as (select a.orderid,a.items,a.groupid,a.groupid as prev_groupid,a.items as runningtotal,1 as basketid,a.rnk from data a where a.rnk=1 union all select a.orderid,a.items,a.groupid ,a.groupid as prev_groupid ,case when b.runningtotal1+a.items<=10 and a.groupid=b.prev_groupid1 then b.runningtotal1+a.items else a.items end as runningtotal ,case when b.runningtotal1+a.items<=10 and a.groupid=b.prev_groupid1 then b.basketid1 else b.basketid1+1 end as baskedid ,a.rnk from data a join cte b on a.rnk=b.rnk1+1 )select orderid1,items1,groupid1,basketid1 from cte