Jeff Moden

Forum Replies Created

Viewing 15 posts - 44,686 through 44,700 (of 59,101 total)

RE: How to select data in t-sql

Yeah... I agree... the written requirements weren't that clear. The desired return posted along with the attempted code and the existing data was the key.
--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)

March 10, 2009 at 1:59 pm

#957065
RE: BULK INSERT & Text Qualifier

Richard Hurst (3/10/2009)
Alternatively you could just do a find and replace on the original file and get rid of them that way.
I just did because the whole thing was driving...
--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)

March 10, 2009 at 1:56 pm

#957060
RE: Transact-sql for changing a telephone number

Spikemarks (3/10/2009)
Absolutely brilliant but they have now thrown a curved ball and state that some of the phone numbers are from other countries and so the zero needs also 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)

March 10, 2009 at 1:53 pm

#957056
RE: nth WeekDayName of a month

robert.mason (3/10/2009)
Jeff Moden (3/6/2009)
If someone ever starts sending you data with milliseconds, you code will produce incorrect results. If some newbie in a hurry sees your code and decides...
--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)

March 10, 2009 at 1:03 pm

#957023
RE: Identifying a record n-rows from the current, on a rolling basis, in a non-contiguous data set

You bet, Todd... Thanks for the feedback. Heh... It's amazing how long the myth of Select/Into has managed to survive.
I'll have to look for it, but I've got a...
--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)

March 10, 2009 at 12:58 pm

#957020
RE: decrypting table contents

Thanks for the kudo, Ross :blush:
I also understand that there are times when the Consultant or other 3rd party wants to protect proprietary code through encryption... that, too, should be...
--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)

March 10, 2009 at 6:27 am

#956716
RE: how can i convert this date'200702071630+1100' in datetime or in string '20070207'

The problem with your tests is that the time to display the results are being included. Also, as you saw in your own testing, converting the date to CHAR(8)...
--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)

March 10, 2009 at 6:16 am

#956706
RE: decrypting table contents

Mohit (3/9/2009)
Yaa Jeff go hunt down the 3rd party vendor :). Jeff likes dealing with them hehe.
Man, it just amazes me that people hire consultants/3rd parties without a contract...
--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)

March 9, 2009 at 10:59 pm

#956573
RE: Are the posted questions getting worse?

Heh... I didn't say anything about evil intent... I just meant to say the original post is correct... the questions are definitely getting worse and more ignorant.
--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)

March 9, 2009 at 10:53 pm

#956571
RE: How to select data in t-sql

... And, if the combination of ID and Countries forms unique pairs, here's another way that's super easy to expand to additional countries...
SELECT ID
FROM #Test
...
--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)

March 9, 2009 at 10:49 pm

#956570
RE: How to select data in t-sql

jmucherla (3/9/2009)
My table structure is actually like this.
TABLE: TEST
ID Countries
100 IN
100 AU
100 GB
100 ...
--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)

March 9, 2009 at 10:44 pm

#956569
RE: How to select data in t-sql

Bevan keighley (3/9/2009)
Or this works as well:
Select t3.ID From #TEST t3 Where t3.COUNTRIES in ('AU', 'IN')
except
Select t3.ID From #TEST t3 Where t3.COUNTRIES not in ('AU', 'IN')
Looks slower based on execution...
--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)

March 9, 2009 at 10:39 pm

#956567
RE: Transact-sql for changing a telephone number

Think not of what to do to the row... think of what to do to the column... and, you don't have to do it all at once... Divide'n'Conquer. 😉
--===== Create...
--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)

March 9, 2009 at 9:47 pm

#956553
RE: Skip 'x' number of rows while reading flat file

You may want to consider loading the data into staging tables first. That way you don't have to worry about such stuff.
--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)

March 9, 2009 at 9:18 pm

#956544
RE: Pivoting Data Without Aggregation

Look closer.... will someone actually have a table name of ['table1, table2']? As it currently stands, this code gives no returns... so what's the actual code and declarations?
--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)

March 9, 2009 at 9:13 pm

#956541

Viewing 15 posts - 44,686 through 44,700 (of 59,101 total)