ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,971 through 5,985 (of 10,143 total)

RE: Recursive select?

demonfox (7/31/2012)
http://www.sqlservercentral.com/Forums/Topic1337063-391-1.aspx
This link doesn't work.
^{“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

July 31, 2012 at 5:30 am

#1519628
RE: Record linkage advice required– Long read (YOU HAVE BEEN WARNED!)

Abu Dina (7/31/2012)
Hi Chris, I have some questions below. Any advice would be much appreciated. In your original post you said:
Try it out. There's always an element of tuning with...
^{“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

July 31, 2012 at 5:28 am

#1519626
RE: Recursive select?

Here's a rCTE to get you started:
;WITH rCTE AS (
SELECT
[Level] = 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(CAST(tr.ParentItemID AS VARCHAR(10)) + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM #tblItemRelationship...
^{“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

July 31, 2012 at 4:44 am

#1519607
RE: Record linkage advice required– Long read (YOU HAVE BEEN WARNED!)

Here you go mate:
CREATE FUNCTION [dbo].[FuzzyMatch_iTVF2k5]
(
@Reference VARCHAR(100),
@Target VARCHAR(100)
)
RETURNS table
AS
-- See also http://research.microsoft.com/pubs/75996/bm_sigmod03.pdf
RETURN
SELECT d.Result, MatchRatio = CAST(CASE
WHEN d.Result = 1 THEN 100
WHEN d.Result = 3 THEN DATALENGTH(@Target)*100.00/DATALENGTH(@Reference)
WHEN d.Result...
^{“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

July 31, 2012 at 3:29 am

#1519588
RE: Record linkage advice required– Long read (YOU HAVE BEEN WARNED!)

Abu Dina (7/31/2012)
Thanks for this Chris but when I tried to create the function I get the following error message:
Msg 156, Level 15, State 1, Procedure FuzzyMatch_iTVF, Line 22
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

July 31, 2012 at 3:13 am

#1519582
RE: Determining the difference between 2 column

Gazareth (7/30/2012)
ChrisM@Work (7/30/2012)
Douglas P. (7/30/2012)
I have:
----------------------January-February-March
GB------London------200------100------120
France--Paris--------300------212------213
And I want to have:
----------------------January-February-March-Difference
GB------London------200------100------120------20
France--Paris--------300------212------213------1
The query you posted above doesn't generate more than one month. Where do the two extra months...
^{“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

July 30, 2012 at 9:11 am

#1519226
RE: Join operation over String non-index fields

That's very kind Paul but I'm just a jobbing TSQL developer! Thanks anyway.
^{“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

July 30, 2012 at 9:03 am

#1519213
RE: Determining the difference between 2 column

Douglas P. (7/30/2012)
I have:
----------------------January-February-March
GB------London------200------100------120
France--Paris--------300------212------213
And I want to have:
----------------------January-February-March-Difference
GB------London------200------100------120------20
France--Paris--------300------212------213------1
The query you posted above doesn't generate more than one month. Where do the two extra months come 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

July 30, 2012 at 9:01 am

#1519210
RE: Determining the difference between 2 column

So your report currently displays only one month's worth of data?
^{“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

July 30, 2012 at 8:35 am

#1519176
RE: Join operation over String non-index fields

Hi Paul, thanks for the plan.
Points:
1. No clustered indexes on any of the 4 tables, a suitable clustered index on DeltaFAKT_DETAIL would probably help the expensive SORT in 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

July 30, 2012 at 8:33 am

#1519174
RE: Determining the difference between 2 column

Douglas P. (7/30/2012)
I should mention one thing. Months that are displayed in the report are determined by parameter @Month, which is determined by the user.
SQL query which provides data looks...
^{“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

July 30, 2012 at 7:28 am

#1519138
RE: Are the posted questions getting worse?

GilaMonster (7/30/2012)
The point is you do it once and check it. He checked and said that the row number does at the moment show the correct order. So you add...
^{“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

July 30, 2012 at 7:21 am

#1519134
RE: Determining the difference between 2 column

SELECT January, February, March, Difference = March - February
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

July 30, 2012 at 7:19 am

#1519132
RE: Join operation over String non-index fields

Hi Paul, can you post the Actual plan please, rather than the estimated plan? There are often differences between the two - for instance, the estimated plan you've just posted...
^{“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

July 30, 2012 at 5:44 am

#1519100
RE: Are the posted questions getting worse?

GilaMonster (7/30/2012)
ChrisM@Work (7/30/2012)
GilaMonster (7/30/2012)
ChrisM@Work (7/30/2012)
Someone's just posted a reply to a request from Gail and my best guess is that she's fallen of her chair laughing. You're a bad lady,...
^{“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

July 30, 2012 at 5:40 am

#1519097

Viewing 15 posts - 5,971 through 5,985 (of 10,143 total)