Jeff Moden

Forum Replies Created

Viewing 15 posts - 42,961 through 42,975 (of 59,102 total)

RE: Intersect, Except, Union, All and Any

I had similar feelings when PIVOT came out. Proves that "new" or "different" is frequently not better, faster, or easier to read. 😛
Good article David. Thanks for taking...
--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)

July 18, 2009 at 5:11 pm

#1026618
RE: Table design

awp (7/18/2009)
Actually they are assessment data of students, such as grades, assessment of a task, comments etc, they are quite varying data which cannot be generated automatically, they are recorded...
--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)

July 18, 2009 at 4:56 pm

#1026615
RE: Table design

In fact, take a look at the following article so you can see more of what I mean... these types of reports can be made to run very fast and...
--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)

July 18, 2009 at 4:03 pm

#1026612
RE: Table design

awp (7/18/2009)
Steve, you may be right about Access using the timestamp column; unless I find another way to create reports, I would have to leave that column alone.
I can’t understand...
--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)

July 18, 2009 at 4:01 pm

#1026611
RE: Clustered Index

First of all, that table is setup for CDR's (Call Detail Records). Rather I should say it's set up incorrectly for CDR's. Almost all of the columns are...
--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)

July 18, 2009 at 12:18 pm

#1026595
RE: Table design

Gosh... even though this is just a reporting table, I still wouldn't add the 10 columns to it unless it were trully normalized data. What is in the 10...
--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)

July 18, 2009 at 11:51 am

#1026592
RE: Set time to default value as zero

Let me come right out and say it... don't use CONVERT to strip the time... it's at least ten times slower than just about any other method.
So far as testing...
--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)

July 18, 2009 at 11:44 am

#1026586
RE: Use sp_MSforeachtable to gen COUNT(*) for each table in MyDBname

Thanks for the feedback, TJ.
--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)

July 18, 2009 at 8:11 am

#1026553
RE: Not sure how to get my subquery right

Absolutely, Lynn. Nicely done.
As a side bar, I used to believe that correlated sub-queries were a form of RBAR and, in many cases, they absolutely are and will 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)

July 18, 2009 at 7:28 am

#1026550
RE: Returning accumulated totals from multiple rows

kylejw (7/18/2009)
Thanks guys,
Yes, I've used Group By but until now I've never looked at Sum from a SQL perspective. Like everything, its quite obvious when you know how 🙂
No problem......
--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)

July 18, 2009 at 7:17 am

#1026548
RE: Calculate a Percentage

dave b (7/17/2009)
Yeah...I do understand...kinda. 😉 I like the CTE solution. I was just thinking that there was a simpler way with less code.
Heh... nah... I was talking about why...
--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)

July 18, 2009 at 7:05 am

#1026546
RE: How To Format Phone Number

tsmith (7/18/2009)
Thank you for your answer. I will try it on Monday. I'm sorry for the double post, but after I asked about it here, I thought maybe I should...
--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)

July 18, 2009 at 7:02 am

#1026544
RE: Not sure how to get my subquery right

Of course, this hasn't been tested because I don't have the table structures nor the test data... but it should be close...
select FiscalMonthDTTM, left(datename(month,FiscalMonthDTTM),3) Mth,
Sum(case when TimeType='YVW Crib' then...
--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)

July 17, 2009 at 11:21 pm

#1026506
RE: Calculate a Percentage

dave b (7/17/2009)
Thanks Bruce! I'll give it a try.
I'm just making sure... you understand why Bruce's solution will work, correct?
--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)

July 17, 2009 at 11:14 pm

#1026505
RE: Stripping all non-numeric chars from phone number and then formatting it.

tsmith (7/17/2009)
Hi. I would like to add a problem that we have at work. Writing a query is not an option. The report we need to generate runs off...
--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)

July 17, 2009 at 11:11 pm

#1026504

Viewing 15 posts - 42,961 through 42,975 (of 59,102 total)