ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,851 through 5,865 (of 10,143 total)

RE: Annoying join problem & frozen brain.

SELECT *
FROM D
LEFT OUTER JOIN a
ON A.a = D.a
WHERE
A.b = 1 -- << this turns the LEFT OUTER JOIN into an INNER JOIN
AND D.a IN (1,2,3,4)
-- use 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

August 21, 2012 at 4:41 am

#1528032
RE: Will indexed view help this query?

-- The query requires a scan of the whole 100M rows to evaluate the ORDER BY.
-- An index with Destination, Origin, Airline and Tax will help.
-- You *probably* don't need...
^{“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

August 21, 2012 at 4:01 am

#1528012
RE: Are the posted questions getting worse?

GilaMonster (8/21/2012)
<soapbox>
Cursors are tools. Use them where they work, don't use them where they don't. Statements like 'you should never use a <whatever>' are just shortsighted
If we count while loops...
^{“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

August 21, 2012 at 2:38 am

#1527985
RE: Today's Random Word!

rhythmk (8/20/2012)
Cliff Jones (8/20/2012)
Scott D. Jacobson (8/20/2012)
SQLRNNR (8/20/2012)
Ray K (8/20/2012)
Manic Monday
The Bangles
Susanna Hoffs and Matthew Sweet.
Ming Tea
Green Tea
Green Day
^{“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

August 21, 2012 at 1:46 am

#1527969
RE: Are the posted questions getting worse?

dwain.c (8/21/2012)
It has been a rare experience for me to post a question to this forum, but I will now. How about a poll?
Yesterday I was called upon 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

August 21, 2012 at 1:45 am

#1527967
RE: Are the posted questions getting worse?

Stefan Krzywicki (8/20/2012)
Brandie Tarvin (8/20/2012)
ChrisM@Work (8/17/2012)
Pet rent? Never heard of that over here.
Landlords in the States are adding it to the usual rental fees as a way to discourage pet...
^{“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

August 21, 2012 at 1:42 am

#1527966
RE: need to fetch the value between two delimiter

SELECT -- resolve "word" between 5th and 6th pipe characters using Cascaded (CROSS) APPLY
WordInRow3 = SUBSTRING(row3,r5.p+1,(r6.p-r5.p)-1)
FROM (SELECT Stringy = @text, LineDelimiter = CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)) d
CROSS APPLY (SELECT p = CHARINDEX(LineDelimiter,Stringy,1)) x1
CROSS...
^{“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

August 20, 2012 at 5:14 am

#1527516
RE: Today's Random Word!

SQLRNNR (8/17/2012)
jasona.work (8/17/2012)
Daniel Bowlin (8/17/2012)
bcsims 90437 (8/17/2012)
Cliff Jones (8/17/2012)
ChrisM@Work (8/17/2012)
SQLRNNR (8/17/2012)
behemoth
Tiger moth
Tiger Shark
Shark week!
Pool shark
Bulette!
(Landshark)
Loan Shark - the worst kind.
Interesting
^{“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

August 20, 2012 at 2:04 am

#1527460
RE: group data by week

jfowler 10337 (8/17/2012)
i read it....my mind just doesn't get computer code lingo all that well. If I posted the data of the tables I'm pulling from it would be...
^{“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

August 20, 2012 at 2:00 am

#1527458
RE: Crazy Interviews

Stuart Davies (8/17/2012)
ChrisM@Work (8/17/2012)
Stuart Davies (8/17/2012)
ChrisM@Work (8/16/2012)
Earlier this year I had an interview for a gig right next to Swindon station, a dream forty-minute commute compared with the two hours...
^{“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

August 20, 2012 at 1:47 am

#1527455
RE: group data by week

jfowler 10337 (8/17/2012)
ok, thanks. I was hoping it would be something as simple as looking at the code and changing something in the grouping. I've inherited the code,...
^{“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

August 17, 2012 at 9:58 am

#1527157
RE: group data by week

jfowler 10337 (8/17/2012)
i just need things grouped by week starting with Monday and ending on Thursday.
There are 13 selects in your query - each one has to be aggregated at...
^{“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

August 17, 2012 at 9:55 am

#1527153
RE: group data by week

jfowler 10337 (8/17/2012)
i just need things grouped by week starting with Monday and ending on Thursday.
No problem.
Run the results into a temp table, index it, and select with an aggregate.
^{“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

August 17, 2012 at 9:44 am

#1527148
RE: Today's Random Word!

SQLRNNR (8/17/2012)
behemoth
Tiger moth
^{“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

August 17, 2012 at 9:35 am

#1527141
RE: Crazy Interviews

Stuart Davies (8/17/2012)
ChrisM@Work (8/16/2012)
Earlier this year I had an interview for a gig right next to Swindon station, a dream forty-minute commute compared with the two hours each way I’d...
^{“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

August 17, 2012 at 8:46 am

#1527119

Viewing 15 posts - 5,851 through 5,865 (of 10,143 total)