ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,791 through 2,805 (of 10,143 total)

RE: Query to find "games behind" in a sports table

Drop GameYear, GameMonth and GameDay if they are derived from GameDate, and calculate them in your queries instead.
What are TxtYear and NumYear? Year (with century) is 4 characters, CHAR(4). 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

November 11, 2014 at 2:18 am

#1757623
RE: Incorrect syntax near the keyword 'ELSE'

I find it easier to use ISNULL:
SELECT (T0.Rate * SUM(T0.LineTotal) - (T0.DiscPrcnt / 100) + ISNULL(T3.LineTotal,0) AS 'Total NATIVE'
😉
^{“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 11, 2014 at 1:46 am

#1757617
RE: Levenshtein UDF performance 'tuning' advise needed

Okay, think I get it. Does this match your output requirement?
SELECT
t1.*,
oa.*
FROM #Vergelijkingstabel_1 t1
OUTER APPLY (
SELECT t3.*, x.[Score %]
FROM #Vergelijkingstabel_2 t3
CROSS APPLY dbo.IF_Levenshtein02 (t3.Omschrijving, t1.Omschrijving) x
WHERE NOT...
^{“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 10, 2014 at 4:07 am

#1757387
RE: Levenshtein UDF performance 'tuning' advise needed

Are you expecting two result sets? If so, here's the second:
-- Using UDF
SELECT
t1.*,
t3.*
FROM #Vergelijkingstabel_1 t1
OUTER APPLY (
SELECT t2.*, x.percentage
FROM #Vergelijkingstabel_2 t2
CROSS APPLY (SELECT percentage = dbo.fn_Levenshtein (t1.Omschrijving, t2.Omschrijving,...
^{“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 10, 2014 at 3:12 am

#1757377
RE: Are the posted questions getting worse?

Eirikur Eiriksson (11/8/2014)
Just got an "interesting" email via SSC, Steve are you expanding the "service"?
😎
She's loaded too! Go for it, Eirikur!
By PM: "Assalamu alaykum
With due respect and humility I write...
^{“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 10, 2014 at 2:15 am

#1757370
RE: Levenshtein UDF performance 'tuning' advise needed

Hi Mike
Working without sample data always comes with the risk of syntax errors - can you prepare some please?
^{“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 10, 2014 at 2:07 am

#1757368
RE: Levenshtein UDF performance 'tuning' advise needed

-- Yes, you are still missing the point I'm trying to make.
-- Also, your strange query is equivalent to this
SELECT
T1.*,
T3.*,
master.dbo.fn_Levenshtein(T1.Omschrijving, T3.Omschrijving, 80) AS percentage
FROM Vergelijkingstabel_1 AS...
^{“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 7, 2014 at 9:33 am

#1757240
RE: Levenshtein UDF performance 'tuning' advise needed

mike--p (11/7/2014)
...
Yes my join was correct, here the results from your query. As you can see it scans through all the columns (plus one too much highligthed in red). 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

November 7, 2014 at 6:53 am

#1757197
RE: Levenshtein UDF performance 'tuning' advise needed

Are you sure your join is correct?
DROP TABLE #Table1
CREATE TABLE #Table1 (Bestelnummer INT, Omschrijving VARCHAR(20))
INSERT INTO #Table1 (Bestelnummer, Omschrijving)
VALUES (101,'ABN101'),(102,'ZBN'),(103,'KDN154'),(104,'ADN235'),(105,'QND999'),(110,'QQQ313'),(112,'ABL513')
-- 7 rows
DROP TABLE #Table2
CREATE TABLE #Table2 (Bestelnummer INT, Omschrijving VARCHAR(20))
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

November 7, 2014 at 5:08 am

#1757168
RE: Levenshtein UDF performance 'tuning' advise needed

mike--p (11/7/2014)
ChrisM@Work (11/6/2014)
SELECT T1.*, T2.*
FROM (SELECT rn = ROW_NUMBER() OVER(ORDER BY something) FROM table_1) AS T1
INNER JOIN (SELECT rn = ROW_NUMBER() OVER(ORDER BY something) FROM table_2) AS T2
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

November 7, 2014 at 1:40 am

#1757141
RE: Levenshtein UDF performance 'tuning' advise needed

SELECT T1.*, T2.*
FROM (SELECT rn = ROW_NUMBER() OVER(ORDER BY something) FROM table_1) AS T1
INNER JOIN (SELECT rn = ROW_NUMBER() OVER(ORDER BY something) FROM table_2) AS T2
ON T1.rn <...
^{“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 6, 2014 at 9:57 am

#1757053
RE: Levenshtein UDF performance 'tuning' advise needed

Try this:
SELECT T1.*, T2.*
FROM table_1 AS T1
INNER JOIN table_2 AS T2
ON T1.[column_T1] < T2.[column_T2]
WHERE dbo.Levenshtein(T1.[column_T1], T2_2.[column_T2]) > percentage
^{“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 6, 2014 at 8:29 am

#1757026
RE: Levenshtein UDF performance 'tuning' advise needed

Sure. That's probably easiest if you post your existing query to use as a model. You may have to post some sample data too, let's see how we get 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

November 6, 2014 at 7:07 am

#1756988
RE: Levenshtein UDF performance 'tuning' advise needed

mike--p (11/6/2014)
I tried the function you posted in that thread, still have the same results. As you stated you have to compare all the rows from both tables with eachother....
^{“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 6, 2014 at 5:38 am

#1756960
RE: Need help with SQL queries.

Can you post up some consumable sample data for the tables please?
In order to write the second query, you will need to know how to track a single commission amount...
^{“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 6, 2014 at 1:53 am

#1756891

Viewing 15 posts - 2,791 through 2,805 (of 10,143 total)