Jeff Moden

Forum Replies Created

Viewing 15 posts - 38,686 through 38,700 (of 59,098 total)

RE: Cast Float to Char with Natural Rounding

Using the great start that Paul provided, the following will also drop the decimal point if a whole number is realized...
USE tempdb;
GO
-- *Inline* table-Itemd function
-- Deterministic, system verified, not precise
GO
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)

April 30, 2010 at 10:05 pm

#1160856
RE: query

ColdCoffee (4/29/2010)
Sri, sorry for saying this, but your question is one of the vaguest i have seen in my stint with SSC..
There is absolutely no information about the emp...
--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)

April 30, 2010 at 9:26 pm

#1160855
RE: query

Homework, Test, or Interview?
--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)

April 30, 2010 at 9:24 pm

#1160854
RE: Cast Float to Char with Natural Rounding

Paul,
Try this...
-- Test with table
DECLARE @data
TABLE (
value DOUBLE PRECISION NULL
);
...
--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)

April 30, 2010 at 8:53 pm

#1160851
RE: Cast Float to Char with Natural Rounding

irobertson (4/30/2010)
Ha really? Lol, that won't work then will it? Complex function it is then.
Do you know what the maximum number of decimal places will ever be for this data?
--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)

April 30, 2010 at 8:30 pm

#1160849
RE: Pivot with Sub Pivot Problem

The two articles that Lutz points out above will show you how to do the "multi-pivot" you seek... and all the reasons not to use it as well as an...
--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)

April 30, 2010 at 8:01 pm

#1160844
RE: SP_EXECUTESQL

Change this...
Declare @SqlStr varchar(max);
... to this...
Declare @SqlStr Nvarchar(max);
... and try again.
--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)

April 30, 2010 at 7:53 pm

#1160843
RE: Minimising use of tempDB during large INSERT

Credits to Navy beans says there's probably a DISTINCT in the offending query and that, behind the scenes, SQL Server is having to make a whole lot more rows due...
--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)

April 30, 2010 at 7:46 pm

#1160841
RE: Query Performance with multiple joins

csillag.yitzik (4/29/2010)
I have a query with multiple inner joins and left joins (about 40). When I run the query it takes about 20 seconds to return 3000 records.
When I get...
--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)

April 30, 2010 at 7:36 pm

#1160840
RE: Table and design advice

p.s. Nice post, Digs. It's always great to see the table and some data.
--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)

April 30, 2010 at 7:05 pm

#1160837
RE: Table and design advice

I'd recommend NOT using a clustered index that has Symbol,Date as the columns and in that order... there will be a pot wad of page splits.
Make Symbol,Date a NON clustered...
--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)

April 30, 2010 at 7:04 pm

#1160835
RE: Data display issue

Cnu2010 (4/30/2010)
For a given County, I am returning the cities in that County
SELECT DISTINCT CITY FROM DEMO WHERE COUNTY = @COUNTY
CITY
-----
EDINA
BLOOMINGTON
EDEN PRAIRIE
SHAKOPEE
How do I display this data in a row...
--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)

April 30, 2010 at 7:00 pm

#1160834
RE: Data display issue

Ninja's_RGR'us (4/30/2010)
How the heck will that work with a country like USA or Canada where you have 1000s of cities?
Heh... one can only hope that the criteria presented is "example...
--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)

April 30, 2010 at 6:59 pm

#1160833
RE: Data display issue

GregoryF (4/30/2010)
Write a cursor.
Fetch each county into it.
Create a variable and keep concat'ing the new county + a space
When the cursor is done, print the variable that contains the string...
--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)

April 30, 2010 at 6:57 pm

#1160832
RE: The value of variable is disappearing in stored procedure

David Webb-200187 (4/30/2010)
Just a shot in the dark without being able to see the code, but if you're using the variable in any concatenation operation, you might want to set...
--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)

April 30, 2010 at 6:55 pm

#1160830

Viewing 15 posts - 38,686 through 38,700 (of 59,098 total)