SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Looping through table


Looping through table

Author
Message
jadelola
jadelola
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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 AmountPurchased
1 20,000
2 30,000
3 40,000
4 200,00

Thanks
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89145 Visits: 41143
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89145 Visits: 41143
jadelola (9/8/2012)
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 AmountPurchased
1 20,000
2 30,000
3 40,000
4 200,00

Thanks


I like to test my answers before I post them but don't always have the time to setup the problem. Take a look at the first link in my signature line below. If you post your data that way, you'll get much better answers more quickly.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jadelola
jadelola
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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 #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
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2111 Visits: 10387
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
jadelola
jadelola
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 154
Hi,

It's not 250.00 but 250.000.
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2111 Visits: 10387
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
jadelola
jadelola
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 154
I hope I got it right this time :-)
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2111 Visits: 10387
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 #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.00,0 UNION ALL
SELECT 2,130000,0 UNION ALL
SELECT 3,500.00,0 UNION ALL
SELECT 4,700.00,577 UNION ALL
SELECT 5,300.00, 0

SELECT * 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search