ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,786 through 4,800 (of 10,143 total)

RE: The Cascading (CROSS) APPLY

Divya Agrawal (4/18/2013)
If there is a csv column having different number of comma's in it, how would CCA help in it. In the examples provided by you we can split...
^{“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 18, 2013 at 5:15 am

#1607643
RE: Count number of visits by week, ending on Saturday

-- Group the data by month and by weeks, where a week runs from sunday to saturday
-- account for a week spanning two months
SELECT
v.SchedProv, x.Weekno, x.[Month],
EarliestApptForWeek =...
^{“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 18, 2013 at 5:09 am

#1607640
RE: The Cascading (CROSS) APPLY

Dwain, Jeff - you guys crack me up! As if I didn't have enough pressure from the escaped dust bunnies 😀
^{“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 18, 2013 at 1:19 am

#1607555
RE: The Cascading (CROSS) APPLY

Divya Agrawal (4/18/2013)
Great post!! thanks for sharing..it is really very useful
Thank you very much, Divya!
^{“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 18, 2013 at 1:15 am

#1607554
RE: Weekly report through whole year

roman 19626 (4/17/2013)
What about if I want same scenario between 1.4.2013 and 14.42013? So each day within this date range is displayed as column?
Can you show what you mean by...
^{“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 18, 2013 at 1:12 am

#1607552
RE: Multiple DMLS in one when clause of MERGE Statement

28.kanikasoni (4/17/2013)
I want to use 2 statements in case of when matched clause
i.e
Update all the values of Vacancy.TB_FTSearchData
also
update the VacancyFTSupdated from table vacancy.TB_Vacancy
The simple answer is -...
^{“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 18, 2013 at 1:10 am

#1607551
RE: The Cascading (CROSS) APPLY

Tim Walker. (4/16/2013)
Interesting, entertaining and educational with worked examples and performance statistics make this a great read.
The fact that it is also genuinely very useful and will help me 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 16, 2013 at 9:29 am

#1606874
RE: The Cascading (CROSS) APPLY

Jeff Moden (4/16/2013)
Awesome job, Chris. Well done!
Thanks Jeff! And thank you for the encouragement to put pen to paper. DC's been nagging me for ages. Once you stepped in...
^{“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 16, 2013 at 9:27 am

#1606872
RE: Need advice on this string manipulate scalar UDF

Here's an iTVF which might do what you want. You will need to check that the xml concatenation can deal with the characters you wish to include:
ALTER FUNCTION [dbo].[ifn_RemoveUnwantedChars]
(@strTarget...
^{“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 16, 2013 at 8:46 am

#1606851
RE: comparing list to master list

Pothiraj R (4/16/2013)
Hi Chris,
Thanks for the query. I didn't use the syntax like this.
FROM (VALUES ('ooo'),('bbb'),('ccc'),('ddd'),('eee'),('fff'),('ggg'),('hhh'),('iii'),('jjj'),('kkk'),('lll')) d (name)
In the same scenario, I will use UNION ALL with SELECT or insert...
^{“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 16, 2013 at 8:17 am

#1606836
RE: comparing list to master list

KoldCoffee (4/16/2013)
Thank you.
Any time.
^{“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 16, 2013 at 8:15 am

#1606833
RE: SQL Query - Actual performance duration for every execution

mrinal.technology (4/16/2013)
Hi,
I have a SQL query with couple of nested queries (query at the bottom for the reference), now the very first time I execute the query on a SQL...
^{“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 16, 2013 at 5:51 am

#1606778
RE: JOIN on MANY to one and ensuring the JOIN is correct

pnr8uk (4/16/2013)
Well it works beautifully so does that mean the productid on the Catalogue table is messed up?
The script looks like a thing of beauty and works nicely....
Intrigued...
^{“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 16, 2013 at 4:47 am

#1606762
RE: JOIN on MANY to one and ensuring the JOIN is correct

pnr8uk (4/16/2013)
Will do Chris - sorry didn't realise there was such a great article as yours, I wondered how so many people got such neat data in the forum.
Will 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

April 16, 2013 at 4:28 am

#1606756
RE: JOIN on MANY to one and ensuring the JOIN is correct

Hi Paul
Can you set up your sample data as ddl and dml please? There are instructions for this in the first article in my sig. Cheers.
^{“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 16, 2013 at 3:07 am

#1606740

Viewing 15 posts - 4,786 through 4,800 (of 10,143 total)