Jeff Moden

Forum Replies Created

Viewing 15 posts - 40,831 through 40,845 (of 59,069 total)

RE: Crosstab query - Please help. it has been a few days without sleep

Heh... I was right. And, I looked up what PIVOT and CHOOSE do (I don't even know how to spell "ACCESS") and I believe we can do 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)

December 3, 2009 at 5:40 pm

#1087443
RE: Fixed length output from numeric column

mister.magoo (12/3/2009)
I've got to ask because I am always looking for the simple solution to things....
If trailing "0"s are allowed, why bother with all that?
Why not just (based on the...
--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)

December 3, 2009 at 4:48 pm

#1087433
RE: Crosstab query - Please help. it has been a few days without sleep

Heh... it's alright, Kevin. I really don't want VPN access because if something goes wrong, then the "new guy" always gets the blame. Besides... there might be 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)

December 3, 2009 at 3:49 pm

#1087422
RE: Totals for each month in a date range query

By the way... here's the next step in turning you from a newbie to an SQL monster... it's what I'll use tonight to solve your problem... heh... unless you beat...
--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)

December 3, 2009 at 2:59 pm

#1087399
RE: Totals for each month in a date range query

You did real good especially for a first timer on such a thing! You've gotten yourself into just a little bit of a problem because you used BETWEEN instead...
--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)

December 3, 2009 at 2:55 pm

#1087398
RE: Crosstab query - Please help. it has been a few days without sleep

Test-170228 (12/2/2009)
Man, you get this right, and I am going to send you a cheque for some pizza money for sure or steak 😉
Hopefully, this will be the final question....
--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)

December 3, 2009 at 2:32 pm

#1087387
RE: Fixed length output from numeric column

I thought of doing that, as well. But if someone stops at the first post (as so often happens), they'd have bad code.
--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)

December 3, 2009 at 2:14 pm

#1087380
RE: Fixed length output from numeric column

Ah... now I see. Somewhere along the line, a space in quotes got dropped when I transferred the code to the forum window. I've edited the previous code...
--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)

December 3, 2009 at 8:47 am

#1087168
RE: Fixed length output from numeric column

Peter Brinkhaus (12/3/2009)
Nice solution, but not entirely correct, I think. What about non-trailing zeroes behind the decimal point i.e. 1234.5078. The result is Modifier=1000, Result=1234507. I little fix will do:
SELECT...
--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)

December 3, 2009 at 8:40 am

#1087154
RE: Converting varchar to int

chandrashekar.2512 (12/1/2009)
Hi Jeff,
ALTER FUNCTION [dbo].[fnSplit](
@InputString varchar(8000)
,@Delimiter varchar(10)
)
RETURNS @TempTable table(
ID int identity(1,1)
,[VALUES] varchar(8000)
)
AS
BEGIN
...
--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)

December 3, 2009 at 8:34 am

#1087149
RE: Converting varchar to int

vikas bindra (11/23/2009)
Nabha (11/23/2009)
Jeff Moden (11/20/2009)
Nabha (11/20/2009)
MY bad! go with Vikas, that is the right solution for you.
Heh... no it's not...that function has a While Loop in it and it's...
--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)

December 3, 2009 at 8:33 am

#1087146
RE: Converting varchar to int

Pemmasani Srinivasa Rao. (12/3/2009)
I think the following is the better code to split the string seperated by comma.
Declare @inputString varchar(1000)
set @inputString='1,2,3,5,6,7,8'
while(charindex(',',@inputString)>0)
begin
print substring(@inputString,1,charindex(',',@inputString,1)-1)
set @inputString=stuff(@inputString,1,charindex(',',@inputString,1),'')
end
Why do you think a loop is better...
--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)

December 3, 2009 at 8:20 am

#1087123
RE: Expressions

😉
--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)

December 3, 2009 at 8:16 am

#1087114
RE: use T-SQL call BCP to load txt into table

The other neat part BULK INSERT is that you can use a "trusted connection"... no need for a user name or password in clear code. Post back if you...
--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)

December 3, 2009 at 8:14 am

#1087112
RE: run query with a string input into an int field

From Books Online...
The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the...
--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)

December 2, 2009 at 8:42 pm

#1086872

Viewing 15 posts - 40,831 through 40,845 (of 59,069 total)