Jeff Moden

Forum Replies Created

Viewing 15 posts - 38,926 through 38,940 (of 59,098 total)

RE: Recursive queries - get the top N quantity

Just convert the Qty from the temp table back to weight and do a final update on the original second table. Don't forget to update the Qty on the...
--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)

April 12, 2010 at 10:16 pm

#1150479
RE: Recursive queries - get the top N quantity

Heh... I was just getting ready to post a nearly identical solution. I'm pretty proud of you guys... you beat me at my own game anymore. 😛 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)

April 12, 2010 at 9:58 pm

#1150475
RE: Recursive queries - get the top N quantity

Yes, it helps a lot. Thanks. Just one final question... is there a column in the second table to show what has been consumed and what has not?
--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)

April 12, 2010 at 8:27 pm

#1150461
RE: TSQL Lookup using BETWEEN (Ip Addresses)

Stefan_G (4/12/2010)
Jeff Moden (4/12/2010)
Stefan_G (4/12/2010)
1) Add a nonclustered index on CountryCode, IpNumber on [my].[ipAddresses]
It'll make for a fine nonclustered index. Using it as a clustered index can cause a...
--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)

April 12, 2010 at 5:56 pm

#1150447
RE: Recursive queries - get the top N quantity

I don't believe it can be done without a wee bit more information. Either the Qty column is missing from the second table or we're supposed to guess that...
--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)

April 12, 2010 at 4:55 pm

#1150425
RE: Is This Odd Behavior? Empty String ('') vs int

GSquared (4/12/2010)
Empty strings = 0. It's kind of arbitrary, but that's how it works.
IsNumeric has a number of flaws, this is just one of them. Check out IsNumeric('1d3')...
--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)

April 12, 2010 at 4:41 pm

#1150420
RE: TSQL Lookup using BETWEEN (Ip Addresses)

Stefan_G (4/12/2010)
Jeff Moden (4/12/2010)
Stefan_G (4/12/2010)
1) Add a nonclustered index on CountryCode, IpNumber on [my].[ipAddresses]
Ohhhh... be careful now. Unless that index is applied after all data is present and no...
--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)

April 12, 2010 at 4:09 pm

#1150413
RE: Using IsNull when passed variable value is ''

sql_avid_fan (4/12/2010)
--delete from folders_rel
where exists (select b.id
from folders_rel a with(nolock)
...
--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)

April 12, 2010 at 1:06 pm

#1150286
RE: Which is better and Why.?

GSquared (4/12/2010)
Jeff Moden (4/12/2010)
Bill Kline-270970 (4/12/2010)
I work in a multi db platform environment. We use both SQL Server and Oracle. I'm just a newbie when it comes to...
--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)

April 12, 2010 at 1:02 pm

#1150280
RE: Clustered index scan versus seek

Robert Frasca (4/12/2010)
The indexes were rebuilt last night, are not fragmented and the statistics are up to date. Additionally, I've forced a recompile of the proc a couple of times.
Did...
--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)

April 12, 2010 at 12:55 pm

#1150270
RE: TSQL Lookup using BETWEEN (Ip Addresses)

Stefan_G (4/12/2010)
1) Add a nonclustered index on CountryCode, IpNumber on [my].[ipAddresses]
Ohhhh... be careful now. Unless that index is applied after all data is present and no new data will...
--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)

April 12, 2010 at 12:51 pm

#1150268
RE: data manipulation by row

NEAL-464478 (4/12/2010)
and how i can create sister table with the specific sort order which will show like 2nd table, i am new to sql any help will be much appreciated.
Thanks
Is...
--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)

April 12, 2010 at 12:47 pm

#1150266
RE: How to build 7 day slots based on start and end dates?

BIMind (4/12/2010)
Jeff...this is pretty close...however this keeps the lowest row ID of any duplicates found and deletes the higher row IDs..i need it the other way around..
i tried row ID...
--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)

April 12, 2010 at 12:45 pm

#1150265
RE: Index suggestion for VERY large table

Stefan_G (4/12/2010)
I actually found such a formula myself by modifying code found at
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66471
Here is the code:
DECLARE @IntDate INT
SELECT @IntDate = 20090117
dateadd(month,((@IntDate/10000-1900)*12)+((@IntDate/100)%100)-1,(@IntDate%100)-1)
But, the performance of this expression is actually slightly...
--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)

April 12, 2010 at 12:41 pm

#1150257
RE: Help with keeping the leading zeros

Based on what Lowell said, my recommendation is that you check the final table for the correct datatype. If you want, the CREATE TABLE statement for both tables... a...
--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)

April 12, 2010 at 12:33 pm

#1150249

Viewing 15 posts - 38,926 through 38,940 (of 59,098 total)