ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,256 through 9,270 (of 10,143 total)

RE: Change a Views data types

Have a look at the ClientID dupes in the bargains table. Are they really different clients, or do they differ in spelling, or missing country code?
Is this a one-off process,...
^{“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

December 5, 2008 at 8:13 am

#908340
RE: Help needed for a twisted SQL query

Did you try this:
DECLARE @Boston_prdID (numeric)
SET @Boston_prdID = 555
SELECT p.*, c.*, d.DistanceToBoston AS [MinDistanceToBoston]
FROM PRODUCTS p
INNER JOIN OTM o ON o.prdID = p.prdID
INNER JOIN CITIES c ON c.postcodeID =...
^{“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

December 5, 2008 at 7:50 am

#908309
RE: Help needed for a twisted SQL query

SELECT @lX = lambertX, @lY = lambertY FROM CITIES WHERE postcodeID = @localID
The definition fior the cities table doesn't include lambertX and lambertY
^{“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

December 5, 2008 at 7:47 am

#908304
RE: Change a Views data types

SELECT *
FROM dbo.Bargains
WHERE CAST(ClientID AS NVARCHAR (10)) IN (
SELECT CAST(b.ClientID AS NVARCHAR (10)) AS ClientID
FROM dbo.Bargains b
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE...
^{“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

December 5, 2008 at 7:41 am

#908297
RE: udate problem

-- Set org_id to be same org_id as manager:
UPDATE p SET org_id = m.org_id
FROM Person p
INNER JOIN Person m ON m.emp_id = p.mgr_id
^{“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

December 5, 2008 at 7:31 am

#908289
RE: Change a Views data types

Thanks...this tells us that none of the rows in the bargains table have a match in the clients table according to the matching methods we've tried.
Time to retry the 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

December 5, 2008 at 7:19 am

#908271
RE: Change a Views data types

A Little Help Please (12/5/2008)
Sorry to be Stupid, but what do you mean by
"just 4 numbers"?
Nah, me stupid not you, sorry.
SELECT COUNT(*)
FROM dbo.Bargains b
--GROUP BY b.Client, b.CountryCode, CAST(b.ClientID 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

December 5, 2008 at 7:02 am

#908259
RE: Help needed for a twisted SQL query

I'm afraid your second query has a problem as well.
Now I get results where all postcodeID's are used for all products
That's the intention, sometimes a quick eyeball is all...
^{“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

December 5, 2008 at 6:58 am

#908257
RE: Help needed for a twisted SQL query

Hi Chris
There's an error in the JOIN, which is corrected here:
DECLARE @Boston_prdID (numeric)
SET @Boston_prdID = 555
SELECT p.*, c.*, dbo.getDistanceFromID(@Boston_prdID, c.latitude, c.longitude) AS [DistanceToBoston], d.DistanceToBoston AS [MinDistanceToBoston]
FROM PRODUCTS p
INNER JOIN...
^{“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

December 5, 2008 at 6:34 am

#908237
RE: Change a Views data types

A Little Help Please (12/5/2008)
Ok i did a stupid one... I forgot to run my SSIS package to populate the tables before I ran the statements.
I have now attched 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

December 5, 2008 at 6:06 am

#908208
RE: Change a Views data types

A Little Help Please (12/5/2008)
Ok i did a stupid one... I forgot to run my SSIS package to populate the tables before I ran the statements.
I have now attched 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

December 5, 2008 at 6:05 am

#908207
RE: Help needed for a twisted SQL query

Yep your absolutely right, this type of query is far more elegant in 2005.
Here's a query which will work.
[font="Courier New"]DECLARE @Boston_prdID (numeric)
SET @Boston_prdID = 555
SELECT p.*, c.*
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

December 5, 2008 at 5:48 am

#908193
RE: Help needed for a twisted SQL query

chris (12/5/2008)
Chris Morris (12/5/2008)
Ok lets confirm this:
Say your reference city is Boston, your requirement would be:
For each and every individual product (which has been sold), list the nearest city 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

December 5, 2008 at 5:34 am

#908180
RE: Help needed for a twisted SQL query

Ok lets confirm this:
Say your reference city is Boston, your requirement would be:
For each and every individual product (which has been sold), list the nearest city to Boston where it's...
^{“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

December 5, 2008 at 5:26 am

#908174
RE: Help needed for a twisted SQL query

Chris,
Do you mean this:
retrieve unique products (prdID) AND the city it's in (postcodeID) AND the shortest distance to a reference city
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

December 5, 2008 at 5:15 am

#908170

Viewing 15 posts - 9,256 through 9,270 (of 10,143 total)