Jeff Moden

Forum Replies Created

Viewing 15 posts - 50,581 through 50,595 (of 59,102 total)

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

srienstr (5/13/2008)
Danielle:
Don't bother with the loop at all, substitute in a join.
----Segment Level Supply
INSERT INTO @ConversionTesting(Quarter, Source, Segment, Metric,Value)
select Q.Quarter, 'Converted' as Source, MarketsegmenttypeCode, 'Inventory', sum(NumUnits)
from dbo.tbl_BldgSegments bs
...
--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 13, 2008 at 2:57 pm

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

Hawkeye67 (5/13/2008)
A great and most helpful article. Keep up the good work.
Thanks, Hawkeye. I appreciate the feedback.
--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 13, 2008 at 2:55 pm

#814808
RE: The CLR

developmentalmadness (5/13/2008)
Splitting a string may be trivial in T-SQL but that doesn't mean it's the best tool for the job. The CLR is vastly faster for splitting strings the longer...
--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 13, 2008 at 9:36 am

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

dporter (5/13/2008)
I need to replace the text that relates to @QuarterEndDate somehow with your idea if possible, but I’m just not sure how to go about it.
Danielle...
Hard to tell because...
--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 13, 2008 at 9:27 am

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

Absolutely correct about the cross-join... change the 11,000 to 1,000,000 and see how fast it works.
--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 13, 2008 at 9:23 am

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

Thanks, David... real good to see that you're still around, too! 🙂
--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 13, 2008 at 8:43 am

#814537
RE: Automating BCP out for all databases

You'll also find a lot of folks that suggest not using them because Microsoft could change them at any time... Heck, they do that with documented features :hehe:
--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 13, 2008 at 8:39 am

#814530
RE: How to find gaps between date ranges

Heh... I never use the first release of anything... not even from Peter 😛
--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 13, 2008 at 8:37 am

#814526
RE: Calculate working hours on shift timings

Just DATEADD the two columns together and then do the simple math.
--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 13, 2008 at 8:19 am

#814503
RE: Ledger Balance Calculation

The only thing that might beat the method in the article would be to maintain a separate table with a list of accounts and the current balance (1 row each)...
--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 13, 2008 at 8:14 am

#814500
RE: Ledger Balance Calculation

quortex (5/13/2008)
have read the suggested article and although it is very informative it is geared towards calculating the total after the data is populated.
That's EXACTLY what you are doing...
--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 13, 2008 at 8:05 am

#814489
RE: "Resource" management design

Why not just ignore the cost for Employees since they suffer no cost? That would cut the number of tables in half and also keep you from having 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)

May 13, 2008 at 7:42 am

#814469
RE: How to find gaps between date ranges

Peso (5/13/2008)
Here is a script for testing, Jeff.
...
I mean no harm in posting the results. I just find it interesting that different methods and approaches to this problem have significantly...
--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 13, 2008 at 7:39 am

#814466
RE: How to find gaps between date ranges

Michael Meierruth (5/13/2008)
I hope you were able to use the SS 2005 solution.
When limited to SS 2000, our queries really start to slow down after a while.
Try the Peso 3...
--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 13, 2008 at 7:33 am

#814457
RE: The CLR

emamet (5/13/2008)
Maybe we can summarise by saying that integrated CLR is the perfect solution...
but that we are still looking for the problem 😛
Just couldn't have said it any better than...
--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 13, 2008 at 7:11 am

#814432

Viewing 15 posts - 50,581 through 50,595 (of 59,102 total)