Looping through table

Question

Looping through table

jadelola

SSC-Addicted

Points: 493
More actions
September 8, 2012 at 10:47 pm

#261297

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

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jadelola SSC-Addicted Points: 493 More actions · Answer 3

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 SSC-Insane Points: 24260 More actions · Answer 4

Customer 2 has 1300.00, which exceeds 250.00. I can't make any sense of your math.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

jadelola SSC-Addicted Points: 493 More actions · Answer 5

jadelola

SSC-Addicted

Points: 493

September 10, 2012 at 6:24 am

#1535643

Hi,

It's not 250.00 but 250.000.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

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.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

jadelola SSC-Addicted Points: 493 More actions · Answer 7

jadelola

SSC-Addicted

Points: 493

September 10, 2012 at 6:59 am

#1535660

I hope I got it right this time 🙂

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

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

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

jadelola SSC-Addicted Points: 493 More actions · Answer 9

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 10

jadelola (9/10/2012)
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

I've no idea. Using your sample data, I get the following:

[font="Courier New"]

ID AmountSold AmountLeftWith RemainderOf250k

1 123.00 0.00 249877.00

2 130000.00 0.00 119877.00

3 500.00 0.00 119377.00

4 700.00 0.00 118677.00

5 300.00 0.00 118377.00

[/font]

Check the AmountSold figures are correct.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

jadelola SSC-Addicted Points: 493 More actions · Answer 11

Hi,

Below is my revised table. Customers 1 and 2 will have 0 left but customer 3 will have 23 left has could only buy 27 units from him. 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.

--===== 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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

jadelola (9/10/2012)
Hi,
Below is my revised table. Customers 1 and 2 will have 0 left but customer 3 will have 23 left has could only buy 27 units from him. 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.

There are at least four ways of writing a query to solve this "Running Totals" problem;

1. Quirky Update - probably the fastest but quite fiddly to set up

2. rCTE - probably the fastest to write

3. Cursor

4. Triangular join - usually the worst performer

This solution uses a recursive CTE.

--========================================================

-- sample data script

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 IDENTITY_INSERT #tmpCustomerAmount OFF

--========================================================

-- rCTE Solution

DECLARE @Limit decimal(13,2) = 250

;WITH Calculator AS (

SELECT

a.id, a.AmountSold,

AmountLeftWith = CAST(CASE

WHEN @Limit > AmountSold THEN 0

ELSE AmountSold - @Limit END AS decimal(13,2)),

LimitLeft = CAST(CASE

WHEN @Limit > AmountSold THEN @Limit - AmountSold

ELSE 0 END AS decimal(13,2))

FROM #tmpCustomerAmount a

WHERE id = 1

UNION ALL

SELECT

a.id, a.AmountSold,

AmountLeftWith = CAST(CASE

WHEN c.LimitLeft > a.AmountSold THEN 0

ELSE a.AmountSold - c.LimitLeft END AS decimal(13,2)),

LimitLeft = CAST(CASE

WHEN c.LimitLeft > a.AmountSold THEN c.LimitLeft - a.AmountSold

ELSE 0 END AS decimal(13,2))

FROM #tmpCustomerAmount a

INNER JOIN Calculator c ON c.id+1 = a.id

)

SELECT

id,

AmountSold,

AmountLeftWith

FROM Calculator

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
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 SSC-Addicted Points: 493 More actions · Answer 13

jadelola

SSC-Addicted

Points: 493

September 11, 2012 at 1:14 pm

#1536447

Awesome thanks 🙂