# Thread: Loop throught table until max amount

1. Registered User
Join Date
Sep 2012
Posts
2

## Unanswered: Loop throught table until max amount

Hi,

I have a table with AmountSold and AmountLeftWith. I have to buy from the customers until the amount bought =250,000.

The max that user can buy is 250,000 so customers 1-3 get left with 0 (AmountLeftWith ) and customer 4 with 577 (AmountLeftWith ) after the update as user couldn't buy the entire amount as it would have exceeded 250,000. Preferably the query should stop afterwards and not proceed to check the other customers.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#tmpCustomerAmount','U') IS NOT NULL
DROP TABLE #tmpCustomerAmount

CREATE TABLE #tmpCustomerAmount (
[id] [int] IDENTITY(1,1) NOT NULL,

AmountSold [decimal](13, 2) NULL,
AmountLeftWith [decimal](13, 2) NULL

)

SET IDENTITY_INSERT #tmpCustomerAmount ON
iNSERT INTO #tmpCustomerAmount (ID, AmountSold)
SELECT '1','123.00',0 UNION ALL
SELECT '2','130,000',0 UNION ALL
SELECT '3','500.00',0 UNION ALL
SELECT '4','700.00' ,577 UNION ALL
SELECT '5','300.00'

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #tmpCustomerAmount OFF

Thanks for the help.

2. Registered User
Join Date
Sep 2001
Location
Chicago, Illinois, USA
Posts
603

You say that the table has an AmountSold and an AmountLeftWith column, but your example only has an AmountSold column.

You make it sound like the customer who transits the 250,000 unit value is the one with the AmountLeftWith value, but in your example, customer #4 is no where near 250,000 units: 123+130,000+50=130,623.

3. Registered User
Join Date
Sep 2012
Posts
2

## Revised table

I need to buy from customers until I reach the limit of 250.000. So from the first 2 customers I can buy all the amount. Customers 1 and 2 will have 0 left but customer 3 will have 23 left has could only buy 27 units from him (if i will buy everything the total will exceed 250.000. The other customers will be left with all their amounts has 250 has been exceeded with customer 3 and therefore cant buy any of their stock/amount.

I will I go about updating AmountLeftWith column (hardcoded in the example)? Thanks

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#tmpCustomerAmount','U') IS NOT NULL
DROP TABLE #tmpCustomerAmount

CREATE TABLE #tmpCustomerAmount (
[id] [int] IDENTITY(1,1) NOT NULL,

AmountSold [decimal](13, 2) NULL,
AmountLeftWith [decimal](13, 2) NULL

)

SET IDENTITY_INSERT #tmpCustomerAmount ON
iNSERT INTO #tmpCustomerAmount (ID, AmountSold,AmountLeftWith )
SELECT '1','123.000',0 UNION ALL
SELECT '2','100.000',0 UNION ALL
SELECT '3','50.000',23.000 UNION ALL
SELECT '4','700.00' ,700.00 UNION ALL
SELECT '5','300.00' , 300.00

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #tmpCustomerAmount OFF

4. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,095