ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,096 through 7,110 (of 10,143 total)

RE: multiple update

-- Slight rewrite using table aliases and IN to slim-down CASE
UPDATE c
SET Amount = CASE WHEN c.lenght IN (5, 10, 15, 30, 45, 60) THEN r.amount ELSE 5000 END
FROM...
^{“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

November 8, 2010 at 5:24 am

#1247592
RE: Today's Random Word!

bcsims 90437 (11/5/2010)
crookj (11/5/2010)
CirquedeSQLeil (11/5/2010)
lazy
lethargic
Joe
... traveling all day Tuesday to get back to work...
Did you run? 😀
Well done - completing a marathon is no mean feat.
^{“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

November 5, 2010 at 10:14 am

#1247215
RE: Are the posted questions getting worse?

CirquedeSQLeil (11/4/2010)
Has anybody here contemplated changing their name (besides bob err ".") for the forums? I am curious if anybody much cares about the confusion that may drive.
Not really,...
^{“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

November 5, 2010 at 7:21 am

#1247113
RE: Are the posted questions getting worse?

GSquared (11/5/2010)
CirquedeSQLeil (11/4/2010)
Grant Fritchey (11/4/2010)
CirquedeSQLeil (11/4/2010)
Has anybody here contemplated changing their name (besides bob err ".") for the forums? I am curious if anybody much cares about the confusion...
^{“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

November 5, 2010 at 6:43 am

#1247078
RE: Row_number() over (partition by ....order by)

andy.roberts (11/5/2010)
Chris Morris-439714 (11/5/2010)
I reckon this will set the whole EMAIL_No column to 1
I agree that with the data you have specified the EMAIL_NO column will always be 1 because...
^{“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

November 5, 2010 at 6:25 am

#1247067
RE: Row_number() over (partition by ....order by)

askquestions (11/1/2010)
Hi guys,
can anyone please explain to me in plain english what this update statement is doing (mainly the join select)
Many thanks
update tblEMAIL
set EMAIL_No = EMAILNUMBER
from tblEMAIL a
JOIN (SELECT EMAIL_ID,
row_number()...
^{“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

November 5, 2010 at 5:25 am

#1247049
RE: Today's Random Word!

Terse
^{“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

November 4, 2010 at 8:51 am

#1246608
RE: Get filtered result set from CTE

Here's another hacky way of doing it. You would benefit from running the results of the CTE into a #Temp table first.
SELECT ISNULL(r0.DataOutPhoneNumber, ISNULL(r1.DataOutPhoneNumber, r2.DataOutPhoneNumber))
FROM #MUConfigCTE r0
LEFT JOIN #MUConfigCTE r1...
^{“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

November 4, 2010 at 8:12 am

#1246561
RE: Today's Random Word!

Ok here goes.
Thor (the god of Thunder) is sitting alone on his cloud, envious of the mortals enjoying themselves below. "S0d this" he thinks, floats down and assumes a body....
^{“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

November 4, 2010 at 7:00 am

#1246503
RE: Today's Random Word!

Brandie Tarvin (11/4/2010)
Thor
Do you know the age-old joke about Thor?
^{“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

November 4, 2010 at 6:39 am

#1246490
RE: Get filtered result set from CTE

Can you please provide a full explanation of the relationship here:
FROM dbo.[Site] s
INNER JOIN dbo.Node n ON s.OwnerNodeID = n.NodeID
^{“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

November 4, 2010 at 6:18 am

#1246475
RE: Get filtered result set from CTE

Hi Chris
Just as a matter of interest, what is the maximum value for RECURSIONLEVEL that ever appears in a result set from this sproc?
^{“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

November 4, 2010 at 5:42 am

#1246462
RE: Loop through string and test if substring of another string

You're welcome. Thank you for the generous feedback 😎
^{“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

November 4, 2010 at 5:32 am

#1246457
RE: Loop through string and test if substring of another string

-------------------------------------------------------------
-- Create a tally table - CAREFUL, this is only 120 rows / numbers
-- and should be used only for this demo
-------------------------------------------------------------
DROP TABLE #Numbers
SELECT TOP 120 n = ROW_NUMBER() OVER(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

November 4, 2010 at 4:41 am

#1246446
RE: Calculate number of moths between two days?

See here.
^{“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

November 3, 2010 at 8:12 am

#1246012

Viewing 15 posts - 7,096 through 7,110 (of 10,143 total)