Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


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

Home » SQL Server 2008 » T-SQL (SS2K8) » Looping through table
Add to briefcase
15 posts, Page 1 of 2
12»»

Looping through table Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
jadelola
Posted Saturday, September 08, 2012 10:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 11:27 AM
Points: 10, Visits: 106
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
Post #1356446
CELKO
Posted Sunday, September 09, 2012 12:55 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
I have a table customer with fields [sic: columns are not fields!] customer_id and “<something>_amount” (DECIMAL) and based on parameters I have to update the amount of each customer. The total amount cannot exceed $250.00.


Why did you post a narrative instead of DDL? We do not even get the name of this table. No keys, your narrative shows dollars, but you use a comma and three decimals. etc. I will guess “gift certificates”, since you cannot be bothered to tell us. Customers and users are all mixed together.

CREATE TABLE Customer_Certificates
(customer_id CHAR(10) NOT NULL PRIMARY KEY,
certificate_amt DECIMAL(5,2) NOT NULL
CHECK (certificate_amt BETWEEN 0.00 AND 250.00));

If a user decides that he wants to buy all the amount that each other user has then the total he buys cannot exceed $250.00.


Look at the CHECK() constraint; this prevent anyone from have more than $250.00 in gift certificates. Users? Customers? Try again with a complete spec and DDL. Oh, SQL programmers hate loops. This is a declarative language and loops are procedural code.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1356502
Jeff Moden
Posted Sunday, September 09, 2012 10:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 32,923, Visits: 26,811
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1356551
Jeff Moden
Posted Sunday, September 09, 2012 10:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 32,923, Visits: 26,811
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1356552
jadelola
Posted Monday, September 10, 2012 5:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 11:27 AM
Points: 10, Visits: 106
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
Post #1356674
ChrisM@home
Posted Monday, September 10, 2012 6:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 921, Visits: 3,748
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
jadelola
Posted Monday, September 10, 2012 6:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 11:27 AM
Points: 10, Visits: 106
Hi,

It's not 250.00 but 250.000.
Post #1356687
ChrisM@home
Posted Monday, September 10, 2012 6:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 921, Visits: 3,748
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
jadelola
Posted Monday, September 10, 2012 6:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 11:27 AM
Points: 10, Visits: 106
I hope I got it right this time
Post #1356706
ChrisM@home
Posted Monday, September 10, 2012 7:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 921, Visits: 3,748
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
Post #1356707
« Prev Topic | Next Topic »

Add to briefcase
15 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.