ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,756 through 4,770 (of 10,143 total)

RE: Error converting varchar to numeric only in where clause

John Mitchell-245523 (4/24/2013)
DevDB (4/24/2013)
Its running fine for me. It returned 29877 when I added CONVERT(numeric(14,2),Value) > 0 in where Clause.
My machine is
Microsoft SQL Server 2008 R2 (SP2) -...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 24, 2013 at 7:53 am

#1609514
RE: Error converting varchar to numeric only in where clause

Try this:
DROP TABLE #Test
CREATE TABLE #Test (ID INT IDENTITY (23,6921), Value VARCHAR(25))
INSERT INTO #Test (Value) VALUES ('3.14'), ('Twenty seven'), ('24/04/2013'), ('100'), ('Yesterday'), ('0')
SELECT *
FROM (
SELECT
ID,
Value,
NumericValue = CAST(Value AS...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 24, 2013 at 7:46 am

#1609507
RE: Function To Add [n] Days to A Date and Adjust to the Nearest Business Date

Jeff Moden (4/24/2013)
Chris,
In reference to the following line of code in your fine function...
E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b CROSS JOIN E1 c), --1M rows...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 24, 2013 at 7:39 am

#1609500
RE: How To Sum...

sharky (4/24/2013)
One set based way of doing it:
DECLARE @Adding TABLE (ID int identity(1,1),Result int,ActualResult int)
INSERT INTO @Adding (Result) VALUES (10),(10),(10),(10),(-10),(-10),(-10),(-10)
SELECT ID, Result, (Select SUM(result) From @Adding A2 WHERE A2.Id...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 24, 2013 at 6:06 am

#1609431
RE: Function To Add [n] Days to A Date and Adjust to the Nearest Business Date

Jeff Moden (4/24/2013)
I really like how easy CROSS APPLY made it to calculate some of the more complex holidays. Well done, Chris.
Thanks Jeff!
One of the points I meant to...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 24, 2013 at 5:55 am

#1609426
RE: Elimination of Table spools

Your third party should read this!
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 24, 2013 at 5:51 am

#1609425
RE: Elimination of Table spools

Jason-299789 (4/24/2013)
I know, and this isn't the worst query, one of them had a 1.2 million Sub-tree cost at the root.
This particular query is used in a...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 24, 2013 at 5:25 am

#1609413
RE: Elimination of Table spools

No worries mate. There's a lot to do here...
Indexing on Policy.SectionLimitExcess - no indexes at all, start with a single-column unique clustered index if possible and relevant. How about SectionID?...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 24, 2013 at 4:49 am

#1609402
RE: Elimination of Table spools

Update statistics on all tables referenced by this query.
Add an index to the table [section] - a unique clustered index on SectionID perhaps?
Run the query, capture and post the...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 24, 2013 at 4:20 am

#1609390
RE: The Cascading (CROSS) APPLY

Amy.G (4/23/2013)
Chris, Thank you for this article. I have been trying to figure out a solution to a problem I've had for a while, and could only come up with...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 24, 2013 at 3:58 am

#1609383
RE: Function To Add [n] Days to A Date and Adjust to the Nearest Business Date

david.holley (4/30/2011)
The issue with company and public holidays is certainly there. The current push is to get the basics out. We'll probably end up with a table for the holidays...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 24, 2013 at 1:52 am

#1609358
RE: Add x amount off days and don't count Weekends

-- itvf parameters
DECLARE @Startdate DATE, @WorkingDays INT
SELECT @Startdate = getdate(), @WorkingDays = 12
-- row generator (see DelimitedSplit2k8 article)
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 23, 2013 at 6:11 am

#1609013
RE: Order By in Sql Server

niladri.primalink (4/23/2013)
yes I want to change it..
Please tell me the process.
However thanks for your reply...
you will have to explain the rules for moving the values around between rows.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 23, 2013 at 4:10 am

#1608989
RE: Order By in Sql Server

niladri.primalink (4/23/2013)
Hi All,
In a table i want to apply order by. My database is 20-20 and table is errorreport where I have only 4 columns (rpt_id, error_id, file_id, errorlino). I...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 23, 2013 at 4:04 am

#1608987
RE: Invalid colum Name

-- details of tables called [student]
-- or columns called [mark2] etc
-- in the 'current' db
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('mark2', 'mark3', 'mark4', 'mark5', 'mark6')
OR...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 23, 2013 at 3:24 am

#1608979

Viewing 15 posts - 4,756 through 4,770 (of 10,143 total)