ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,661 through 9,675 (of 10,143 total)

RE: How to use column name inlike clause in SQL server

Can you provide the necessary logic for one join to be selected? One option you could use is to LEFT JOIN for each of the three possible joins and pick...
^{“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 22, 2008 at 3:46 am

#861737
RE: removing appended dash

You're welcome ChrisT, thanks for the feedback.
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 20, 2008 at 10:22 am

#860765
RE: removing appended dash

Use LIKE in the join:
[font="Courier New"]
SELECT a.zipcode, z.zipcode
FROM AgentAddresses a
LEFT JOIN ZipCodes z ON a.zipcode LIKE z.zipcode+'%'[/font]
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 20, 2008 at 8:37 am

#860661
RE: Date Format

What client software are you using to display the date to the user? Is the default display of datetime provided by this client (and using regional settings) inappropriate for your...
^{“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 20, 2008 at 4:56 am

#860523
RE: Date Format

Sudarsan, it's difficult to tell exactly what you mean. One interpretation of your requirements is as follows:
You have dates in an unknown format. These dates are in table columns.
You want...
^{“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 20, 2008 at 4:31 am

#860513
RE: How to update joined tables

UPDATE c SET hazard_info = h.hazard_phrase
FROM compound c
INNER JOIN hazard h on h.compound_id = c.compound_id
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 20, 2008 at 4:03 am

#860506
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Jeff Moden (8/19/2008)
Chris Morris (8/19/2008)
Really nice to read, Jeff, and absolutely spot on.
I've had to do pivots in the last couple of weeks, with an unknown number of output (transposed)...
^{“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 20, 2008 at 2:24 am

#860477
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Really nice to read, Jeff, and absolutely spot on.
I've had to do pivots in the last couple of weeks, with an unknown number of output (transposed) columns with unknown names,...
^{“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 19, 2008 at 7:42 am

#860016
RE: SQL Brain

Chad Crawford (8/15/2008)
I'll be back after I finish running DBCC CHECKBRAIN
Chad
This one runs nightly, sometimes with irritating sound effects.
^{“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 17, 2008 at 5:34 am

#859158
RE: Sorting value in a column

Sergiy (8/7/2008)
Christopher Stobbs (8/7/2008)
ah Chris my bad,
I'm 2005, wierd that the results are different it must do with the Percent and Order by clause combination.
Ankur
What is your default...
^{“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 8, 2008 at 12:52 am

#854936
RE: eliminating duplicates

Gosh Jeremy :blush: thanks!
^{“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, 2008 at 9:29 am

#854542
RE: Condition in JOIN clause

Christopher Stobbs (8/7/2008)
Chris,,,
You making me nervious...
are you following me around today :w00t:
Be afraid...be very afraid...
Nah Chris I'm picking the easy ones, same as you 😛 😀
^{“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, 2008 at 8:27 am

#854487
RE: Condition in JOIN clause

VB (8/7/2008)
thanks for the reply, however this wont achieve my goal, becuase if the "key" is not null i want a inner join on the tables if not jus 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

August 7, 2008 at 8:17 am

#854477
RE: Sorting value in a column

Christopher Stobbs (8/7/2008)
ah Chris my bad,
I'm 2005, wierd that the results are different it must do with the Percent and Order by clause combination.
Thanks for pointing this out Chris, 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

August 7, 2008 at 7:17 am

#854426
RE: Sorting value in a column

Christopher Stobbs (8/7/2008)
HI Chris,
I copied your code straight into a a query window and here are the results I code
TheValue(No column name)
132132
873873
969969
Thanks Chris...how odd! I've tested it here several times...
^{“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, 2008 at 6:24 am

#854391

Viewing 15 posts - 9,661 through 9,675 (of 10,143 total)