ChrisM@Work

Forum Replies Created

Viewing 15 posts - 10,036 through 10,050 (of 10,143 total)

RE: Find the intersection of multiple table

Chris
Try this. There's quite a few elements of this which can be adjusted to fit your data, for instance increasing the token length from 3 will add greater weight 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

August 15, 2007 at 1:42 am

#726973
RE: Find the intersection of multiple table

Chris, can you post some representative sample data? Datatype of the column, Min / max length of contents and min/max word count would be good also.
Cheers
ChrisM
^{“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

August 14, 2007 at 11:40 pm

#726966
RE: Find the intersection of multiple table

That's interesting, and not at all what I was expecting. You're matching on one or more words in a varchar column in each table?
I'm doing fuzzy deduping on company...
^{“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

August 14, 2007 at 1:00 pm

#726891
RE: Find the intersection of multiple table

Chris
I came across this problem recently (the same interview perhaps) and came up with the following. Three tables, 1 2 & 3, which share a pk across some rows in each table....
^{“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

August 14, 2007 at 9:18 am

#726834
RE: Weirdest interview question?

<<Similar types of answers help you hire similar types of people.>> I hadn't thought of that and you're spot on.
They asked if there was a particular area of TSQL...
^{“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

August 9, 2007 at 8:32 am

#725740
RE: Weirdest interview question?

Heh heh had this one yesterday <<Why are man hole tops round?>> at an interview for a gig with a mortgage company. I answered "so they fit" and "'cos a...
^{“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

August 9, 2007 at 2:49 am

#725644
RE: Do what I said?

Hi Loner
I reckon Brandie has nailed this one - if the US market is anything like the market here in the UK, then there's a real shortage of good people...
^{“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

August 7, 2007 at 4:48 am

#725070
RE: Complicated view

No worries mate, good luck.
^{“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

August 2, 2007 at 5:03 am

#724125
RE: Complicated view

Hey Goodguy, I'm not explaining this well, sorry.

WHERE ...
AND IM.LOCID = CASE WHEN @LOCID > 0 THEN @LOCID ELSE IM.LOCID END
This means "if @LOCID is greater than zero then use...
^{“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

August 1, 2007 at 8:51 am

#723896
RE: Complicated view

If the default value for @LOCID will be non-null, then you can do essentially the same substitution with a CASE instead:
WHERE IM.LOCID = CASE WHEN @LOCID > 0 THEN @LOCID ELSE IM.LOCID END
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

August 1, 2007 at 7:59 am

#723868
RE: Complicated view

Cool, you're well on the way.
You can use ISNULL in the WHERE clause as follows:
CREATE TABLE #LOCATIONS (LOC_ID int, LOCATION varchar(10))
INSERT INTO #LOCATIONS
SELECT 1, 'POOL BAR' UNION ALL
SELECT 2, 'RESTAURANT'...
^{“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

August 1, 2007 at 7:00 am

#723841
RE: Complicated view

Hi Goodguy
It's an uphill task because the sproc doesn't fit the sample data or vice versa. Please can you amend one or the other to fit your requirements? I suspect...
^{“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

August 1, 2007 at 5:35 am

#723813
RE: Complicated view

Hi Goodguy, of course, you're making perfect sense, thank you for the excellent explanation.
Check out this query, have a look at the result set, I'm sure you will find it...
^{“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, 2007 at 9:11 am

#723624
RE: Complicated view

Hi Goodguy, please can you QC the following? I've taken a couple of liberties e.g. introduced 'CUP' as a unit for 'TEA'.
Cheers
ChrisM
DROP TABLE #FOODS
CREATE TABLE #FOODS (FOOD_ID int, DES varchar(20),...
^{“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, 2007 at 8:13 am

#723592
RE: Complicated view

Thanks for the words of advice Kenneth, I'm a newbie here and still learning the ropes. I'll bear in mind what you've said.
Cheers
ChrisM
^{“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, 2007 at 5:23 am

#723527

Viewing 15 posts - 10,036 through 10,050 (of 10,143 total)