You could try the following
I'm sure that this can be improved though and I don't know if it will scale very well as it is recursive.
IF OBJECT_ID('TempDB..#table','U') IS NOT NULL
DROP TABLE #table
CREATE TABLE #table
(
tableID int NOT NULL,
runningCount int not null,
orderCol int,
FieldToSum float
)
IF OBJECT_ID('TempDB..#table2','U') IS NOT NULL
DROP TABLE #table2
CREATE TABLE #table2
(
tableID int NOT NULL,
runningCount int not null,
orderCol int,
FieldToSum float
)
insert #table
select1, 0, 1, 4.733415944 union all
select2, 0, 8, 4.733415944 union all
select3, 0, 6, 4.733415944 union all
select4, 0, 19, 4.733415944 union all
select5, 0, 14, 6.950871733 union all
select6, 0, 3, 6.942781406 union all
select7, 0, 2, 6.956403557 union all
select8, 0, 20, 6.945977457 union all
select9, 0, 11, 6.956403557 union all
select10, 0, 13, 6.956403557 union all
select11, 0, 4, 6.945977457 union all
select12, 0, 10, 6.956403557 union all
select13, 0, 17, 6.945977457 union all
select14, 0, 12, 6.956403557 union all
select15, 0, 15, 6.968313001 union all
select16, 0, 5, 6.968313001 union all
select17, 0, 7, 7.024537923 union all
select18, 0, 18, 7.024537923 union all
select19, 0, 16, 6.968313001 union all
select20, 0, 9, 6.968313001
CREATE UNIQUE INDEX [Idx1] ON #table(orderCol)
;WITH
maxordercol as (SELECT MAX(orderCol) endorder FROM #table),
a AS (
SELECT TableID, orderCol, FieldToSum,
FieldToSum RunTotal,
1 runningCount,
'A' direction
FROM #table
WHERE orderCol = 1
UNION ALL
SELECT t.TableID, t.orderCol, t.FieldToSum,
case when t.FieldToSum + a.runTotal <= 25 then
t.FieldToSum + a.runTotal
else
t.FieldToSum
end RunTotal,
case when t.FieldToSum + a.runTotal <= 25 then
a.runningCount
else
a.runningCount + 1
end runningCount,
'A' direction
FROM a
inner join #table t on t.orderCol = a.orderCol + 1
),
b as (
SELECT TableID, orderCol, FieldToSum,
FieldToSum RunTotal,
1 runningCount,
'D' direction
FROM #table
WHERE orderCol = (select endorder from maxordercol)
UNION ALL
SELECT t.TableID, t.orderCol, t.FieldToSum,
case when t.FieldToSum + b.runTotal <= 25 then
t.FieldToSum + b.runTotal
else
t.FieldToSum
end RunTotal,
case when t.FieldToSum + b.runTotal <= 25 then
b.runningCount
else
b.runningCount + 1
end runningCount,
'D' direction
FROM b
inner join #table t on t.orderCol = b.orderCol - 1
),
c AS (
select tableid, runningCount, ordercol, fieldtosum
from (
select *, row_number() OVER (partition by tableid order by runningCount) seq
from (select * from a union all select * from b) x
) y
where y.seq = 1
)
INSERT INTO #table2
SELECT *
FROM c
OPTION (MAXRECURSION 0)
UPDATE #table
SET runningCount = b.runningCount
FROM #table a INNER JOIN #table2 b ON a.tableid = b.tableid
I tried it over 1200 records as well and the performance was 40ms vs 4200ms