ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,901 through 3,915 (of 10,143 total)

RE: Union Join between 3 SQL Queries

Why read ALL OF THOSE TABLES three times when you can get your results by reading them only once?
Why include tons of columns in your subquery - then throw them...
^{“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 13, 2013 at 2:04 am

#1673183
RE: Union Join between 3 SQL Queries

Take care with your join types. Referencing a column from an outer-joined table will convert the join to an IJ (except the special case of checking a column for NULL).
SELECT...
^{“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 12, 2013 at 7:16 am

#1672944
RE: How to validate data as per user requirement

avdhut.k (12/12/2013)
Hi,
My requirement is to validate the data,as per users specification,
...
What's the context? Is this user data entry, data import, data migration? 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 12, 2013 at 7:03 am

#1672938
RE: error with stored procedure

SELECT
@QTRSTRTDATE = DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0),
@QTRENDDATE = DATEADD(m,DATEDIFF(m,-1, GETDATE()),-1)
EXEC @return_value = [dbo].[test] @QTRSTRTDATE, @QTRENDDATE
^{“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 12, 2013 at 6:53 am

#1672934
RE: Set based super challenge

dwain.c (12/11/2013)
Jeff Moden (12/11/2013)
ChrisM@Work (12/11/2013)
Gosh, I'm surprised that with a title like this, there have been so few takers - and so little feedback from the OP. Maybe those sleepless...
^{“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 12, 2013 at 6:45 am

#1672932
RE: Set based super challenge

dwain.c (12/12/2013)
ChrisM@Work (12/12/2013)
-- I might give it a go over lunch.
----------------------------------------------------------------------------------------
I can see it now. Cornish pasty in one hand while the other furiously types in the 1M row...
^{“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 12, 2013 at 4:38 am

#1672890
RE: Set based super challenge

lotusnotes (12/11/2013)
Thank you to all.
In a fit of inspiration I have come up with my own solution. Although it gives a slightly different result set, I'd be interested 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

December 12, 2013 at 2:14 am

#1672864
RE: Set based super challenge

Gosh, I'm surprised that with a title like this, there have been so few takers - and so little feedback from the OP. Maybe those sleepless nights have caught up....
^{“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 11, 2013 at 6:58 am

#1672609
RE: SELECT question

gary.morey (12/11/2013)
The SSN is stored as a MD5 hashed value.
The combination of SSN, PKT and FICE_NBR in table A does not have a match on the combination of SSN, PKT...
^{“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 11, 2013 at 6:54 am

#1672608
RE: Update Cursor Advice

There's an alternate interpretation of the question: the first match between the two tables (on OrderID, mpn) takes the first line number, the second match takes the second number and...
^{“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 11, 2013 at 2:08 am

#1672524
RE: Set based super challenge

This doesn't look too challenging. Can you correct both your sample data and your expected output for EpisodeNo 10010? Cheers.
For those of you looking for sample data to play 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

December 10, 2013 at 6:55 am

#1672298
RE: SELECT question

Aram Koukia (12/9/2013)
gary.morey (12/9/2013)
I have 2 tables. Each one has the following columns: SSN, PKT and FICE_NBR.
I need to be able to retrieve the rows from table 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

December 10, 2013 at 1:49 am

#1672225
RE: SELECT question

SELECT DISTINCT *
FROM Tablea a
WHERE NOT EXISTS (
SELECT 1
FROM Tableb b
WHERE b.SSN = a.SSN
AND b.PKT = a.PKT
AND b.FICE_NBR = a.FICE_NBR
)
^{“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 10, 2013 at 1:42 am

#1672224
RE: Conditional where

derekmcdonnell3 (10/24/2013)
I made a mistake, it is in the ON statement that must be conditional, and only on that one field. The data is too messy to get 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

December 6, 2013 at 4:53 am

#1671460
RE: Are the posted questions getting worse?

GilaMonster (12/4/2013)
In unrelated news, slightly belated drum rolls please..
<---------
Drum rolls with chocolate. Very few redundant words in all those posts.
^{“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, 2013 at 6:26 am

#1671041

Viewing 15 posts - 3,901 through 3,915 (of 10,143 total)