Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Looping through table Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, September 8, 2012 10:47 PM
 Grasshopper Group: General Forum Members Last Login: Friday, November 20, 2015 6:50 AM Points: 16, Visits: 154
 Hi,I have to write a query and i am not sure how to go about it. I have a table customer with fields customerid and amount (decimal) and based on parameters I have to update the amount of each customer. The total amount can't exceed \$XX.XX (eg \$250.000). If user decides that he wants to buy all the amount that each user has than the total he buys can't exceed \$250.000. So that means in the example below that he can buy from customers 1-3 all the amount but from the last customer he can only buy 160,000 has 20,000+20,000+30,000+130,000=290 and if from the last customer he would buy 200,000 the total will exceed the limit of 250.The user doesn't select which customer he wants to buy from. It's random. Maybe I will order it by ID.How can I write the stored procedure?Example:ID AMOUNT AmountPurchased1 20,0002 30,0003 40,0004 200,00Thanks
Post #1356446
 Posted Sunday, September 9, 2012 10:38 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 11:17 AM Points: 42,046, Visits: 39,424
 CELKO (9/9/2012)Look at the CHECK() constraint; this prevent anyone from have more than \$250.00 in gift certificates. Gosh, Joe. I'm pretty sure that has nothing to do with the problem at hand. Read the narrative again. This would appear to be a running total problem. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." Helpful Links:How to post code problemsHow to post performance problems
Post #1356551
 Posted Sunday, September 9, 2012 10:42 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 11:17 AM Points: 42,046, Visits: 39,424
Post #1356552
 Posted Monday, September 10, 2012 5:44 AM
 Grasshopper Group: General Forum Members Last Login: Friday, November 20, 2015 6:50 AM Points: 16, Visits: 154
 Hi,Sorry for the mess. Here is the code. The max that user can buy is 250.000 so customers 1-3 get left with 0 and customer 4 with 577 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 #tmpCustomerAmountCREATE TABLE #tmpCustomerAmount ( [id] [int] IDENTITY(1,1) NOT NULL, AmountSold [decimal](13, 2) NULL, AmountLeftWith [decimal](13, 2) NULL )SET IDENTITY_INSERT #tmpCustomerAmount ONiNSERT INTO #tmpCustomerAmount (ID, AmountSold) SELECT '1','123.00' ,0 UNION ALLSELECT '2','130.000' ,0 UNION ALLSELECT '3','500.00' ,0 UNION ALLSELECT '4','700.00' ,577 UNION ALL SELECT '5','300.00' --===== Set the identity insert back to normal SET IDENTITY_INSERT #tmpCustomerAmount OFF
Post #1356674
 Posted Monday, September 10, 2012 6:05 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 8:52 AM Points: 1,149, Visits: 9,646
 Customer 2 has 1300.00, which exceeds 250.00. I can't make any sense of your math. Low-hanging fruit picker and defender of the moggies For better assistance in answering your questions, please read this. Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1356679
 Posted Monday, September 10, 2012 6:24 AM
 Grasshopper Group: General Forum Members Last Login: Friday, November 20, 2015 6:50 AM Points: 16, Visits: 154
 Hi,It's not 250.00 but 250.000.
Post #1356687
 Posted Monday, September 10, 2012 6:41 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 8:52 AM Points: 1,149, Visits: 9,646
 jadelola (9/10/2012)Hi,It's not 250.00 but 250.000.Your figures are not in a consistent format, you are interchanging '.' and ',' as the thousands separator. It's making this excercise confusing. Can I suggest you use the US/UK convention for money 999,999,999.00 throughout? Use three significant digits if you wish but please be consistent. Low-hanging fruit picker and defender of the moggies For better assistance in answering your questions, please read this. Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1356695
 Posted Monday, September 10, 2012 6:59 AM
 Grasshopper Group: General Forum Members Last Login: Friday, November 20, 2015 6:50 AM Points: 16, Visits: 154
 I hope I got it right this time
Post #1356706
 Posted Monday, September 10, 2012 7:03 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 8:52 AM Points: 1,149, Visits: 9,646
 jadelola (9/10/2012)I hope I got it right this time Here's your DDL and DML adjusted so it doesn't throw an error and doesn't implicitly convert numbers as text into decimal. Run it, check the figures are what you expect, if not then amend and post back:`--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#tmpCustomerAmount','U') IS NOT NULL DROP TABLE #tmpCustomerAmountCREATE TABLE #tmpCustomerAmount ( [id] [int] IDENTITY(1,1) NOT NULL, AmountSold [decimal](13, 2) NULL, AmountLeftWith [decimal](13, 2) NULL )SET IDENTITY_INSERT #tmpCustomerAmount ONiNSERT INTO #tmpCustomerAmount (ID, AmountSold, AmountLeftWith) SELECT 1,123.00,0 UNION ALLSELECT 2,130000,0 UNION ALLSELECT 3,500.00,0 UNION ALLSELECT 4,700.00,577 UNION ALL SELECT 5,300.00, 0SELECT * FROM #tmpCustomerAmount ` Low-hanging fruit picker and defender of the moggies For better assistance in answering your questions, please read this. Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1356707
 Posted Monday, September 10, 2012 7:24 AM
 Grasshopper Group: General Forum Members Last Login: Friday, November 20, 2015 6:50 AM Points: 16, Visits: 154
 Thanks How do I calculate the AmountLeftWith field? I wrote it hardcoded now in the example but that is what the acutal result should be.Thanks
Post #1356720

 Permissions