Query Help!

Question

Post reply

Query Help!

ganeshmuthuvelu

SSCrazy

Points: 2177
More actions
December 27, 2008 at 12:29 am

#383643

Hello:

I have a table like this:

min_value, max_value, allowed_value

0, 10, NULL

10, 15, NULL

15, 18, NULL

18, 25, NULL

I have a value in a variable 23. I have to split this varaible value and update the allowed_value column like this:

min_value, max_value, allowed_value

0, 10, 10

10, 15, 5

15, 18, 3

18, 25, 5

such that the variable value 23 is split in the allowed_value column between the min and max values.

Can someone help me in writing this update query without cursors?

Thanks!

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

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I’m sorry but I don’t understand how you split the value 23. According to what you wrote it should be a number between the min_value and max_value, but according to your example it isn’t. Can you explain it again pleas?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
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/

Dugi SSCoach Points: 17998 More actions · Answer 2

interesting variable value ...at first when I saw your sample data it shows me the logic that max_value - min_value = allowed_value but the last record doesn't have this logic!

we need more info here ...or maybe somebody has experience to find in magic way ...:w00t:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

ps. One Orange Chip Points: 29255 More actions · Answer 3

ps.

One Orange Chip

Points: 29255

December 27, 2008 at 7:04 am

#918017

yeah!! Whats the logic of splitting 23 into 4 rows. the sum of minimum values in the four rows comes to 43...

Pradeep Singh

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Nah... the logic is pretty simple if you look at it right... the 23 is not being split by Min value... it's being split by the difference between the Min and Max value for each row.

If the number were 14 instead of 23, the distribution would be as follows...

min_value, max_value, allowed_value

0, 10, 10

10, 15, 4

15, 18, 0

18, 25, 0

If the number were 16 instead of 23, the distribution would be as follows...

min_value, max_value, allowed_value

0, 10, 10

10, 15, 5

15, 18, 1

18, 25, 0

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Dugi (12/27/2008)

interesting variable value ...at first when I saw your sample data it shows me the logic that max_value - min_value = allowed_value but the last record doesn't have this logic!

we need more info here ...or maybe somebody has experience to find in magic way ...:w00t:

Sure it does... 5 fits in the difference between 18 and 25... the reason it's not 7 is because the rest of the 23 was used up and only 5 remained for this "bin".

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

ganeshmuthuvelu (12/27/2008)

Can someone help me in writing this update query without cursors?

Yep... and we don't need a WHILE loop, either (it would be just as slow)...

First things first... here's the way you should post data to get better answers quicker... keeps folks like me from having to do it for everyone... see the link in my signature line below for how to do bigger examples of data...

--===== Create an populate a test table.



     -- This is NOT the solution to the problem

CREATE TABLE #Test

        (

        Min_Value     INT,

        Max_Value     INT,

        Allowed_Value INT

        )

 INSERT INTO #Test

        (Min_Value,Max_Value,Allowed_Value)

 SELECT  0, 10, NULL UNION ALL

 SELECT 10, 15, NULL UNION ALL

 SELECT 15, 18, NULL UNION ALL

SELECT 18, 25, NULL

Ok... now, to control the order of the update to the table, we need to have a clustered index on the Min_Value column. So let's add that...

--===== First, add the necessary index to the table



     -- to control the order of the "quirky" update

 CREATE UNIQUE CLUSTERED INDEX IXC_#Test_Min_Value

     ON #Test (Min_Value)

Then, using the same "running total" logic found in the following article...

[font="Arial Black"]Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5[/font][/url]

... we end up with this little slice of cursorless computational heaven also known as a "pseuedo cursor update" or "quirky update"...

--===== Solution to problem starts here



--===== Define a couple of variables to keep track of things

DECLARE @Balance INT, --Keeps track of amount to distribute

        @Allowed INT  --The amount just distributed

 SELECT @Balance = 23, --<< The amount from the original problem

        @Allowed = 0

--===== ... and, solve the problem...

 UPDATE #Test

    SET @Allowed = Allowed_Value = CASE WHEN @Balance >= Max_Value - Min_Value

                                        THEN Max_Value - Min_Value

                                        WHEN @Balance > 0

                                        THEN @Balance

                                        ELSE 0

                                        END,

        @Balance = @Balance - @Allowed

   FROM #Test WITH(INDEX(0))

--===== Show the results

SELECT * FROM #Test

It works just like calculating a running balance for a check book in something like visual basic except you don't have to read a row, process a row, write a row... because of the clustered index scan caused by WITH(INDEX(0)), it does all that at once and in the correct order.

Please read the article I pointed you to for more details... there's a lot of caveat's to suffer if you don't do it exactly right. And, yes, it absolutely MUST be a clustered index. I cover that in the article, as well.

--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)
Intro to Tally Tables and Functions

Dugi SSCoach Points: 17998 More actions · Answer 7

Dugi

SSCoach

Points: 17998

December 27, 2008 at 10:35 am

#918047

:hehe: I told you ganeshmuthuvelu

that someone will do like magic man ...now here is Jeff amazing code generator!

:w00t:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

ps. One Orange Chip Points: 29255 More actions · Answer 8

ps.

One Orange Chip

Points: 29255

December 27, 2008 at 11:15 am

#918058

by the time anyone even thinks of going in the right direction, the solution arrives at lightening speed!! Thats Jeff :hehe:

Pradeep Singh

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Thanks, guys. :blush: It's amazing to me how often a problem can be resolved by the "running total" method.

--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)
Intro to Tally Tables and Functions

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 10

ganeshmuthuvelu

SSCrazy

Points: 2177

December 27, 2008 at 3:11 pm

#918083

Thanks, Jeff!. You got it exactly right. The logic was to get the running total.

I was wondering how CTE's (Common Tabl expressions) in SQL Server 2005 will be of more help to handle such running sum kind of scenarios.

How good this query will be when CTE's are used?. Any thoughts/help?.

Thanks!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

"Steping" through rows with a CTE can be done... but it will be much slower because recurrsion is worse than a simple cursor or while loop... and we both know how bad those two can be.

The method I used is capable of updating a million rows in less than 7 seconds. If anyone has a CTE that can solve a running total problem at that speed, I sure would like to see it because it would be a marvel to behold.

--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)
Intro to Tally Tables and Functions

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 12

Lynn Pettis

SSC Guru

Points: 442447

December 27, 2008 at 6:50 pm

#918102

Jeff Moden (12/27/2008)

Thanks, guys. :blush: It's amazing to me how often a problem can be resolved by the "running total" method.

I told you it has value outside of running totals. It is amazing what you can do with the prinicles behind it.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 13

Excellent solution and excellent article. Thank you for sharing both of them.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
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/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Thanks, Adi. Feedback like that keeps me going. 🙂

--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)
Intro to Tally Tables and Functions