asked on

Sql server 5 -Loop through a table counting values

Im using sql server 5 and I want a query to loop through a table adding a value in each row to a counter and break when i reach a max value - eg TotalQty

eg table
'records'
name qty
lou 1
paul 2
alan 1
jess 3
.. loop through this table and stop when adding qty in each row = TotalQty

eg TotalQty = 4

should return counter = 3
as it would have looped through rows 1 to 3
if eg TotalQty = 2 it should return counter = 2

pseudocode eg
select * from records
counter = 0
recordcounter = 0

while counter < TotalQty
counter = counter + table.qty
recordcounter = recordcounter + 1
wend

any ideas welcome

slightwv (䄆 Netminder)

This is in the SQL and Query Topic Areas: What database?

If this is a database, I think you can do it in the SQL itself and not need any loop.

We need more information: Tables, sample data and better defined expected results form the sample data.

If this isn't a database, what product/language/??? are you using?

Louise

ASKER

sql server 5

table 'records' defined above
my expected result would be the value 'counter' would be 3 after going through loop if
TotalQty = 4

eg loop run though 1
counter = 1 -- counter +( records.qty)1
eg loop run though 2
counter = 3 -- counter +( records.qty)2
eg loop run though 3
counter = 4-- counter +( records.qty)1

ste5an

First of all: SQL is set based. Thus you need an order criteria. Your sample has none.

Then: SQL Server is out of life cycle. This is a sever security issue.

Thus: A SQL Server 2012+ solution..

DECLARE @Sample TABLE (
    PersonName NVARCHAR(255) NOT NULL ,
    QuantityNumber INT NOT NULL ,
    OrderNumber INT NOT NULL
);

INSERT INTO @Sample ( PersonName ,
                      QuantityNumber ,
                      OrderNumber )
VALUES ( 'lou', 1, 1 ) ,
       ( 'paul', 2, 2 ) ,
       ( 'alan', 1, 3 ) ,
       ( 'jess', 3, 4 );

DECLARE @Threshold INT = 4;

-- Data.
WITH RunningSum
AS ( SELECT S.PersonName ,
            S.QuantityNumber ,
            SUM(S.QuantityNumber) OVER ( ORDER BY S.OrderNumber ASC ) AS RunningSum
     FROM   @Sample S )
SELECT *
FROM   RunningSum RS
WHERE  RS.RunningSum <= @Threshold;

-- "Counter".
WITH RunningSum
AS ( SELECT S.PersonName ,
            S.QuantityNumber ,
            SUM(S.QuantityNumber) OVER ( ORDER BY S.OrderNumber ASC ) AS RunningSum
     FROM   @Sample S )
SELECT COUNT(*) AS [Counter]
FROM   RunningSum RS
WHERE  RS.RunningSum <= @Threshold;

Open in new window

Louise

ASKER

Thanks Ste5an, Ive no control over the type of server the client uses unfortunately they havent upgraded from 5

I pasted your code into my query window, got the errors

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'order'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'order'.

ASKER CERTIFIED SOLUTION