Jeff Moden

Forum Replies Created

Viewing 15 posts - 50,566 through 50,580 (of 59,091 total)

RE: PIVOT working incorrectly?

Matt Marston (5/13/2008)
The PIVOT statement can be useful at times, but it is handy to know that anything that can be done with a PIVOT statement can be converted 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 4:58 pm

#814868
RE: PIVOT working incorrectly?

benjamin.nelson06 (5/13/2008)
Very interesting as I have been working on doing a similar thing now for a few days. However I needed a Grand Total Column and a Grand Total...
--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 4:54 pm

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

srienstr (5/13/2008)
Jeff Moden (5/13/2008)
Why do you need a Cross-Join on the @DistinctQuarters table?
From my read of the code, it appeared to be storing (or intended to store) multiple time frames,...
--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 4:24 pm

#814854
RE: Ledger Balance Calculation

quortex (5/13/2008)
Firstly with my previously pasted in code which looked at the TOP entry and added the new amount I would like to try this with OPTION(MAXDOP 1). However 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 13, 2008 at 3:06 pm

#814822
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

Viewing 15 posts - 50,566 through 50,580 (of 59,091 total)