ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,386 through 2,400 (of 10,143 total)

RE: Are the posted questions getting worse?

GilaMonster (6/25/2015)
I found a photo of my Siamese.
*sniff*
I'm very sorry for your loss.
^{“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

June 25, 2015 at 6:54 am

#1808029
RE: Combined Ranking

bugg (6/25/2015)
ChrisM@Work (6/25/2015)
Yes, or this which looks a little cleaner:
SELECT
rn = ROW_NUMBER() OVER(PARTITION BY s.fruit ORDER BY s.Preference),
max_rn = COUNT(*) OVER(PARTITION BY s.fruit),
s.*,
p.OrderQuantity
INTO #StreamedData
FROM @tmp s
INNER JOIN @purchase...
^{“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

June 25, 2015 at 4:34 am

#1807988
RE: Combined Ranking

Yes, or this which looks a little cleaner:
SELECT
rn = ROW_NUMBER() OVER(PARTITION BY s.fruit ORDER BY s.Preference),
max_rn = COUNT(*) OVER(PARTITION BY s.fruit),
s.*,
p.OrderQuantity
INTO #StreamedData
FROM @tmp s
INNER JOIN @purchase p
ON...
^{“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

June 25, 2015 at 4:01 am

#1807982
RE: Combined Ranking

bugg (6/25/2015)
ChrisM@Work (6/24/2015)
I wonder how a rCTE version would fare, in terms of performance?
Very cool 🙂
I've amended the code to pull in unassigned order quantities similar to spaghetti's output 🙂
DECLARE...
^{“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

June 25, 2015 at 3:11 am

#1807968
RE: Get Prior Max of Quantity

sreeram.sivakumar 61990 (6/24/2015)
...
The prior max should be calculated based on source and destination keys.
Please list the rules for the calculation.
^{“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

June 25, 2015 at 3:02 am

#1807966
RE: Are the posted questions getting worse?

dwain.c (6/25/2015)
BWFC (6/25/2015)
Chattering?! What a great word for that vocalization! I know immediately what you are talking abut.
Did you read that somewhere or come up with it on your own?...
^{“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

June 25, 2015 at 2:00 am

#1807953
RE: What is wring with my SQL ? Why am I getting the same item repeated ?

mw112009 (6/24/2015)
...THE SQL syntax was fine!
The query is at stage 1 of the three stages of SQL development:
1. Make it work
2. Make it fast
3. Make it pretty (formatting and documentation)
Don't...
^{“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

June 25, 2015 at 1:47 am

#1807951
RE: What is wring with my SQL ? Why am I getting the same item repeated ?

I think your query should look something like this:
SELECT
T.RDW_PATIENT_ID,
x.PE_DVT
FROM #t T
CROSS APPLY (
SELECT STUFF( (
SELECT ',[<' + CONVERT( CHAR(8), s.ActivityDate, 112 ) + '>' + s.TermDescription +...
^{“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

June 24, 2015 at 10:03 am

#1807819
RE: Pivot, unpivot

It's a bad idea as Drew points out, but it's not difficult to do. It's an unusual requirement and often it's a step in a process which would be difficult...
^{“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

June 24, 2015 at 8:50 am

#1807779
RE: Actual text value of Null

samartel (6/24/2015)
Quick one for you all. I dont have access to test this or I would but I know you all are quick to help a brother out.
I have...
^{“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

June 24, 2015 at 8:26 am

#1807763
RE: Combined Ranking

spaghettidba (6/24/2015)
ChrisM@Work (6/24/2015)
I wonder how a rCTE version would fare, in terms of performance?
Brilliant! Works like a treat!
I've recently implemented a solution here which is essentially the same as 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

June 24, 2015 at 6:59 am

#1807723
RE: Tuning SQL Server query

Minaz Amin (6/24/2015)
I will test on DEV first and follow up with all the env.
Can you post the Actual Execution Plan as a .sqlplan attachment? This would help folks evaluate...
^{“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

June 24, 2015 at 6:04 am

#1807712
RE: Combined Ranking

I wonder how a rCTE version would fare, in terms of performance?
DECLARE @tmp TABLE (fruit VARCHAR(50), shop VARCHAR(10), preference INT, InStock INT)
INSERT INTO @tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 1',1 ,4)
INSERT INTO...
^{“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

June 24, 2015 at 5:35 am

#1807708
RE: Are the posted questions getting worse?

Jeff Moden (6/22/2015)
Eirikur Eiriksson (6/22/2015)
ChrisM@Work (6/22/2015)
Alvin Ramard (6/22/2015)
GilaMonster (6/22/2015)
It's starting to look like the replication which keeled over last week did so because the passwords on the distributor were changed....
^{“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

June 24, 2015 at 3:43 am

#1807684
RE: Are the posted questions getting worse?

Brandie Tarvin (6/23/2015)
So I know it's dirty pool to promote one's own blog on the forums, but in this case, I absolutely MUST show off.
Click it and you'll see why....
^{“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

June 24, 2015 at 3:32 am

#1807683

Viewing 15 posts - 2,386 through 2,400 (of 10,143 total)