Jeff Moden

Forum Replies Created

Viewing 15 posts - 40,861 through 40,875 (of 59,102 total)

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

Test-170228 (12/3/2009)
Hi Jeff,
This is what I have when I check for the version value on my server
"Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52...
--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 6:51 pm

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

TheSQLGuru (12/3/2009)
Jeff, you silly person! My big smiley indicated that my pushing R2 was a JOKE. 🙂 I think it is in CTP3 stage or so ......
--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 6:49 pm

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

TheSQLGuru (12/3/2009)
This is gonna be a monster SUM(CASE ...) query, but definitely doable as long as the column values are known. Have fun Jeff. Something like this is...
--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 6:08 pm

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

Viewing 15 posts - 40,861 through 40,875 (of 59,102 total)