ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,831 through 1,845 (of 10,143 total)

RE: Joining Tables with Right Function

Jeff Moden (1/18/2016)[hrCan we agree on "SARGable in one direction only" as it's often sufficient to permit tweaking a decent plan?
With the battles I'm currently facing at work on 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

January 18, 2016 at 5:48 am

#1852484
RE: Joining Tables with Right Function

VegasL (1/15/2016)
Thanks Luis, the last one appears straightforward, concatenation with fixed characters. However when I try on actual table without the # temp tables, i get incorrect syntax...
^{“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

January 18, 2016 at 4:59 am

#1852474
RE: Need help with SQL to get duplicate rows and still show unique fields in the rows and filter by NULL field when one or both are NULL

wolfgheist (1/14/2016)
ChrisM@Work (1/14/2016)
wolfgheist (1/14/2016)
I am playing around with it, but it keeps pulling too much data. It is pulling 20k+ rows, when my first script above pulls 4302 rows that...
^{“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

January 14, 2016 at 9:02 am

#1851966
RE: Need help with SQL to get duplicate rows and still show unique fields in the rows and filter by NULL field when one or both are NULL

wolfgheist (1/14/2016)
I am playing around with it, but it keeps pulling too much data. It is pulling 20k+ rows, when my first script above pulls 4302 rows that contains all...
^{“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

January 14, 2016 at 8:47 am

#1851957
RE: Need help with SQL to get duplicate rows and still show unique fields in the rows and filter by NULL field when one or both are NULL

wolfgheist (1/14/2016)
I am playing around with it, but it keeps pulling too much data. It is pulling 20k+ rows, when my first script above pulls 4302 rows that contains all...
^{“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

January 14, 2016 at 8:43 am

#1851955
RE: Need help with SQL to get duplicate rows and still show unique fields in the rows and filter by NULL field when one or both are NULL

wolfgheist (1/14/2016)
Hi Chris,
Thanks for the suggestion, but I cannot get that to show what I need.
It is Pulling
MedicalRecordNumberAccountNumberFirstNameLastNameWhenDischarged
12345 ...
^{“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

January 14, 2016 at 8:11 am

#1851939
RE: Need help with SQL to get duplicate rows and still show unique fields in the rows and filter by NULL field when one or both are NULL

Experiment with the result set. Take out the rn=1 filter so you can see the full set.
^{“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

January 14, 2016 at 7:59 am

#1851929
RE: Need help with SQL to get duplicate rows and still show unique fields in the rows and filter by NULL field when one or both are NULL

Have a play with ROW_NUMBER(). This should be close:
SELECT MedicalRecordNumber, AccountNumber, FirstName, LastName, WhenDischarged
FROM (
SELECT p.MedicalRecordNumber, p.AccountNumber, p.FirstName, p.LastName, p.WhenDischarged,
rn = ROW_NUMBER() OVER(PARTITION BY p.MedicalRecordNumber, p.AccountNumber ORDER BY p.WhenDischarged)
FROM Patient...
^{“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

January 14, 2016 at 7:17 am

#1851894
RE: Salary vs contract rate?

Jeff Moden (12/27/2015)
BWAAAA-HAAA!!! To0 funny! But, Nah... a "wheelie" on a unicycle is the hop you need to do to go up the stairs.
How do you...blimey Jeff, 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

January 11, 2016 at 2:06 am

#1850894
RE: the use of CTEs

erics44 (1/8/2016)
ChrisM@Work (1/8/2016)
erics44 (1/8/2016)
Hugo Kornelis (1/8/2016)
erics44 (1/8/2016)
Hugo Kornelis (1/8/2016)
Jeff Moden (1/7/2016)
Alan.B (1/6/2016)
You can't write a recursive CTE with using pre-CTE features.
Technically correct because only an rCTE can be an...
^{“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

January 8, 2016 at 4:02 am

#1850443
RE: the use of CTEs

erics44 (1/8/2016)
Hugo Kornelis (1/8/2016)
erics44 (1/8/2016)
Hugo Kornelis (1/8/2016)
Jeff Moden (1/7/2016)
Alan.B (1/6/2016)
You can't write a recursive CTE with using pre-CTE features.
Technically correct because only an rCTE can be an rCTE but...
^{“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

January 8, 2016 at 3:29 am

#1850440
RE: the use of CTEs

Jason A. Long (1/7/2016)
...
Edit: As a side note... When I included TVFs in that statement, I was referring to the "parameterized view" type of TVF... Not a TVF that's added...
^{“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

January 8, 2016 at 1:39 am

#1850415
RE: Unpredictable results

Eric M Russell (1/7/2016)
Here you appear to be selecting TOP X rows without an explicit sort order. I'm assuming that ORDER BY (SELECT NULL) is essentially nullifying the sort order,...
^{“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

January 7, 2016 at 10:05 am

#1850260
RE: Unpredictable results

Jacob Wilkins (1/7/2016)
There have been a few problems like this that seem tied to parallelism. Running with the clustered index and OPTION (MAXDOP 1) yields deterministic results again.
There was 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

January 7, 2016 at 9:44 am

#1850247
RE: Loop through an IN() clause?

There are some code gotcha's in your original query. Looking at the first CTE, and expanding the range of PartID to get a decent sample set for testing, shouldn't this:
SELECT...
^{“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

January 7, 2016 at 9:05 am

#1850232

Viewing 15 posts - 1,831 through 1,845 (of 10,143 total)