Jeff Moden

Forum Replies Created

Viewing 15 posts - 31,861 through 31,875 (of 59,099 total)

RE: how to group and rank (with reset)

sjsubscribe (11/22/2011)
Jeff Moden (11/20/2011)
@sjsubscribe,
Post the CREATE TABLE statement for you table and any/all the indexes you may have on the table and I'll show you the "Quirky Update" method Cold...
--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)

November 25, 2011 at 5:44 pm

#1413080
RE: insert and expand table according particular condition

Wow! My appologies. I lost track of this thread. Let me know if you still need help with this and I'll try to get to it before...
--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)

November 25, 2011 at 5:24 pm

#1413078
RE: When is a scan cheaper than a seek?

GilaMonster (11/24/2011)
Jeff Moden (11/24/2011)
perhaps some experimentation with blocking needs to be done with utility tables, such as the Tally Table.
Those kind of tables should be read-only in use (I don't...
--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)

November 25, 2011 at 5:01 am

#1412958
RE: Create a Tally or Numbers Table

ben.mcintyre (11/24/2011)
Jeez Jeff, I knew you'd been doing Tally tables for a long time, but you wrote the article THAT long ago ?
Talk about nativity ...
😀
--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)

November 24, 2011 at 5:09 pm

#1412818
RE: How to modify code to display '21:30:00 PM' as '09:30:00 PM'?

We should frame this post as one of the reasons to not double post. 😀
--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)

November 24, 2011 at 4:13 pm

#1412812
RE: how to get filename of latest file in a directory with t/sql?

Oh, shoot. My apologies. I lost track of this thread. Here's the solution given the file name pattern you posted. I hope I'm not too terribly...
--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)

November 24, 2011 at 4:08 pm

#1412811
RE: Formating numbers

It's pretty much as I stated... instead of formatting data, pass the data as the correct data type.
--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)

November 24, 2011 at 3:13 pm

#1412807
RE: Data Rollup

Sam Bendayan (11/21/2011)
Yep, you're right...didn't catch that subtlety in the requirements. This should do the trick:
select yr,
month,
member,
(select count(distinct code)
from #testtable t3
where t1.yr =...
--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)

November 24, 2011 at 2:54 pm

#1412802
RE: Cummulative percents

Amy.G (10/19/2011)
I did see the changes, and hopefully incorporated them. I should go back and read it again though; I was so excited it worked, but realize I don't fully...
--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)

November 24, 2011 at 2:49 pm

#1412800
RE: CTEs

Appoligies for the late reply but I guess I'll have to agree to disagree with you, Jeff. In most cases, the number of rows directed to a temp table...
--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)

November 24, 2011 at 2:44 pm

#1412799
RE: advice on archiving data

PaulB-TheOneAndOnly (11/22/2011)
All reporting should happen in a database other than OLTP either a "Reporting" database or a properly structured "Data Warehouse"
Although that's the general concensus, I've found many cases where...
--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)

November 24, 2011 at 2:04 pm

#1412791
RE: Advice on running totals using a cursor

My most sincere appologies for the late reply. Thank you very much for the feed back.
--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)

November 24, 2011 at 2:01 pm

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

That would do it! Thanks for 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)

November 24, 2011 at 9:43 am

#1412761
RE: total inside cte

Apologies for the very late reply but thank you very much for posting all the work you did on this.
--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)

November 24, 2011 at 9:40 am

#1412758
RE: When is a scan cheaper than a seek?

GilaMonster (11/5/2011)
Jeff Moden (11/4/2011)
bteraberry (11/3/2011)
I agree with G^2. I would rather error on the side of reducing reads over picking the "fastest" time unless it is really disproportionate.
I'd have...
--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)

November 24, 2011 at 9:18 am

#1412745

Viewing 15 posts - 31,861 through 31,875 (of 59,099 total)