Jeff Moden

Forum Replies Created

Viewing 15 posts - 50,701 through 50,715 (of 59,099 total)

RE: Calculating time between 2 dates minus time from week(s) end(s)

Peso (5/7/2008)
Michael, what if starttime and endtime is NOT on weekend as op wrote originally?
Jeff, what if starttime AND endtime is ON weekend as op wrote originally?
Dunno.... I wrote 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)

May 8, 2008 at 12:47 am

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

Cool... thanks for the feedback, Eric. Sounds like a great CLR! Any chance of twisting your arm into posting the source code for it?
Also... what did you use...
--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)

May 8, 2008 at 12:41 am

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

Well, folks... I guess that's just about it for tonight. I'd also like to say thanks to...
Christopher Ford
David McKinney
humbleDBA
Cory Ellingson
Manie Verster
curbina
tbeadle
JJ B
... for...
--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)

May 8, 2008 at 12:27 am

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

Matt Miller (5/7/2008)
What - only 12 useful tips? in a single article?:hehe:
Stop it now - you're setting the bar too high for the rest of us....
(superlative as usual).....
Ah, my ol'...
--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)

May 8, 2008 at 12:23 am

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

Kevin Kennedy (5/7/2008)
Really enjoyed the article.
However... on the parameter split if parameter list happens to be exactually 8000 characters the substring in the select will fail. For example:
DECLARE @Parameter...
--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)

May 8, 2008 at 12:18 am

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

Eric Wilson (5/7/2008)
Great article.
I used an "Integers" table for years, but starting with 2005 found that CLR functions to stream out integers is better (faster and takes virtually no space...
--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)

May 8, 2008 at 12:05 am

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

Wayne West (5/7/2008)
Excellent article, Jeff! I came across an interesting date table article on Tech Republic that was linked here yesterday after it re-surfaced at BuilderAU.Com.au, I updated 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)

May 7, 2008 at 11:14 pm

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

Steve Rosenbach (5/7/2008)
Jeff, once again, you are my hero!!
Beautifully-written article about such a useful topic.
I've used a "dates" table for quite a while, and actually have a tally table in...
--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)

May 7, 2008 at 11:06 pm

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

RyanRandall (5/7/2008)
Mark (5/7/2008)
Jeff Marcus (5/7/2008)
If you are using SQL Server 2005 you can use ROW_NUMBER instead.
If you're not using SQL 2005, I guess you could simulate ROW_NUMBER like this...
declare @param...
--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)

May 7, 2008 at 10:54 pm

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

Hugo Kornelis (5/7/2008)
buktseat (5/7/2008)
This is probably a rediculous question, but how can any of your "using the tally table" examples work if you don't declare N? I guess I...
--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)

May 7, 2008 at 10:33 pm

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

GSquared (5/7/2008)
Excellent article, Jeff. Good introduction.
I've been using Numbers tables for a couple of years now, and I just keep finding more uses for them all the time.
Examples include:...
--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)

May 7, 2008 at 10:30 pm

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

JohnG (5/7/2008)
As always, a great article from Jeff.
As for performance testing of various techniques by anyone, I am continually amazed as to the incorrect methods for generating sample data.
Example (taken...
--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)

May 7, 2008 at 10:26 pm

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

GSquared (5/7/2008)
I ran some speed and load tests on this code.
Summary: While the CTE works, and is fast by any normal standard, the Numbers version is even faster, and requires...
--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)

May 7, 2008 at 10:15 pm

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

GSquared (5/7/2008)
Hugo:
Just SP is fine (though I personally dislike the habit - as if anything other than a stored procedure might follow an EXEC keyword). It's SP_ (with an...
--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)

May 7, 2008 at 9:59 pm

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

Carl Federl (5/7/2008)
From the article, you wrote " Tally table .. starting at 0 or 1 (mine start at 1)"
There are advantages for having the Tally table include 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)

May 7, 2008 at 9:55 pm

#812204

Viewing 15 posts - 50,701 through 50,715 (of 59,099 total)