Jeff Moden

Forum Replies Created

Viewing 15 posts - 41,776 through 41,790 (of 59,067 total)

RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Heh... ok. I just did a preview of the book on Amazon. They called it a "Pivot" table because of it's use in splitting data from a single...
--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)

September 26, 2009 at 1:17 pm

#1058769
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Thanks for the feedback, Greg.
About 95% of what I do with Tally tables need a Tally table that starts with 1. That's why I haven't started mine at 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)

September 26, 2009 at 1:06 pm

#1058767
RE: SQL Server 2000 Related Query

You've missed my point. I realize that Column C needs to be in ascending order. Does the order in which they appear according to column B need 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)

September 26, 2009 at 12:56 pm

#1058761
RE: convert number into words

raaz66 (9/25/2009)
Hi
I want to convert number into words in sql server 2005.For examople,we enter number like 15000 from front end ,but it should be save into database like...
--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)

September 26, 2009 at 11:19 am

#1058750
RE: Split String and ordering

Yes... step 1 would be to post some data in a readily consumable format. That will do two things... it'll let us see the type of data you're trying...
--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)

September 26, 2009 at 11:16 am

#1058749
RE: SQL Server 2000 Related Query

It's not possible unless you have another column that maintains the order of the original data or you aways want the data in descending order according to column B (according...
--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)

September 26, 2009 at 10:57 am

#1058746
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

That's a shame... Hugo's code fixes the problem and you would have seen it.
--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)

September 25, 2009 at 11:09 am

#1058586
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

DavidL (9/25/2009)
And no - there is no reason to use ORDER BY every time you use the tally table. There is reason to use ORDER BY every time you expect...
--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)

September 25, 2009 at 10:59 am

#1058578
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

DavidL (9/25/2009)
Jeff, I'm sure you never expected anyone to up and create a tally table with 16m records, but, there I was with a slow afternoon on my hands...
Heh... it's...
--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)

September 25, 2009 at 10:03 am

#1058542
RE: Calculating the Number of Business Hours Passed Since a Point of Tme

I get it... you're not running the same code as in the article. Please post the code you are running. Thanks.
--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)

September 24, 2009 at 5:56 pm

#1058322
RE: Calculating the Number of Business Hours Passed Since a Point of Tme

I ran your code from the previous post... here it is, again...
select getdate()
select dbo.CalcTimeBetweenTwoDates('2004-11-16 15:30', '2007-11-21 07:31')
select getdate()
Here's what I get for an error message from that code...
Msg 4121, Level...
--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)

September 24, 2009 at 5:51 pm

#1058321
RE: Calculating the Number of Business Hours Passed Since a Point of Tme

Thanks for the feedback, Mazharuddin... I'll take a look.
--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)

September 24, 2009 at 5:41 pm

#1058319
RE: Very slow SQL query.

David Kranes (9/24/2009)
Here is the DDL. Thank you.
There real problem is that the "Active_Address_ID" has no index on it nor is it a part of any other index, yet...
--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)

September 24, 2009 at 5:19 pm

#1058315
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Almost forgot... if you're just playing with a Tally table, then TempDB is fine. If you want one for a production table, then Hugo is correct there... TempDB 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)

September 24, 2009 at 5:02 pm

#1058313
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

DavidL (9/24/2009)
A reasonably sized (12000 rows) tally table took care of
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
--===== Find the min and max dates in the range of data
SELECT...
--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)

September 24, 2009 at 4:59 pm

#1058311

Viewing 15 posts - 41,776 through 41,790 (of 59,067 total)