ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,326 through 8,340 (of 10,143 total)

RE: "OR" statement in JOIN query

I've always used this method:
SELECT a.*, b1.*, b.*
FROM tblNewData a
LEFT OUTER JOIN tblExistingData b1
ON b1.GROUPNUMBER = a.GROUPNUMBER AND b1.SSN = a.SSN
LEFT OUTER JOIN tblExistingData b
ON a.GROUPNUMBER =...
^{“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 21, 2009 at 8:03 am

#1094781
RE: Temporary tables

What have you done so far? Even if this is homework you may still get help with it, especially if you can demonstrate a willingness to do at least some...
^{“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 21, 2009 at 7:44 am

#1094766
RE: My code

shriramrc 75244 (12/21/2009)
Hi here is my code....
So what? We've known for years that the answer is 42.
^{“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 21, 2009 at 7:06 am

#1094733
RE: Invalid column name error

Your output list includes a column with an alias not used in the query (which uses aliases a, g and h):
SELECT a.IPEquipmentID IPEquipmentDataRowID, a.IPEquipmentName, a.IPEquipmentTypeID,
g.ManagementIPAddress, g.VoiceIPAddress, p.HDFReference,
h.IPTVStateID 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 21, 2009 at 4:47 am

#1094676
RE: Data to be split in different column

sandy-833685 (12/18/2009)
Its a simple insert statement Its not possible for me to insert inverted commas for each statement, you can go through the sample data
common understanding is i am having...
^{“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 18, 2009 at 5:44 am

#1093977
RE: Data to be split in different column

sandy-833685 (12/18/2009)
I had provided the sample data only
Sandy, please review and adjust the following sample code to match your sample data.
CREATE TABLE #Sample (Col1 CHAR(3), Col2 VARCHAR(60), Col3 VARCHAR(60))
INSERT INTO...
^{“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 18, 2009 at 5:05 am

#1093966
RE: Better Query

ningaraju.n (12/18/2009)
yes...you are right
Restate your requirements - this is a very straightforward query.
^{“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 18, 2009 at 4:29 am

#1093952
RE: Better Query

ningaraju.n (12/18/2009)
In my examples there are two groups
Group1: Java,Perl
Group2: Java,PHP
If an Candidate has both java and perl knowledge and then he has the full knowledge of Group1 then he...
^{“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 18, 2009 at 4:19 am

#1093949
RE: Better Query

ningaraju.n (12/18/2009)
In my examples there are two groups
Group1: Java,Perl
Group2: Java,PHP
If an Candidate has both java and perl knowledge and then he has the full knowledge of Group1 then he...
^{“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 18, 2009 at 3:43 am

#1093943
RE: Better Query

ningaraju.n (12/18/2009)
Friend ,
Thanks for your Query,I tried and compare with the other possibilities that i have mentioned in post, but yours taking more reads than other possibilities.
Please check it once
Can...
^{“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 18, 2009 at 3:04 am

#1093931
RE: Better Query

ningaraju.n (12/17/2009)
Moe is winne because he has the skills PHP and java which belongs to Group 2
Must a winner have all the skills of the group?
^{“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 18, 2009 at 2:03 am

#1093910
RE: How to get the PRIOR Record in a data set

Here's one way to do it - I suspect the performance would really suck on a large data set though.
SELECT pt.dtCheckIn, pt.dtCheckOut, pt.sGuestFName, pt.sGuestLName, ct.*
FROM (
SELECT CurrentID =...
^{“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 16, 2009 at 10:18 am

#1093185
RE: Best Practices for - Showing Detail And Aggregate Values together

I'd go along with Jack on this one.
^{“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 16, 2009 at 7:42 am

#1092999
RE: Parent and Child Data Grouping

What do you want your output to look like, using the sample data you've provided?
^{“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 16, 2009 at 7:37 am

#1092993
RE: Better Query

ningaraju.n (12/16/2009)
--I need the names of Candidates who are having skills belonging to one group
Can you explain how Moe is a "winner"?
INSERT INTO #Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO #Candidates...
^{“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 16, 2009 at 7:08 am

#1092971

Viewing 15 posts - 8,326 through 8,340 (of 10,143 total)