Results 1 to 4 of 4
  1. #1
    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. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Please restate your problem.

    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.

    Something does not add-up here.

    Please restate your problem.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    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. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I have looked at this a number of times and am confused by three things:
    • Why are you buying from customers? Most businesses buy from suppliers and sell to customers.
    • Are you using "." as a decimal point or a thousands separator? The data that you've provided is not clear on this.
    • You have columns for amounts sold and remaining, but not the original balance. Therefore, how can you calculate the amount remaining?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •