ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,461 through 2,475 (of 10,143 total)

RE: Eliminate Duplicate Rows when joining multiple tables

tstagliano (6/8/2015)
We have the below query which is pulling in Sales and Revenue information. Since the sale is recorded in just one month and the revenue is recorded each...
^{“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

June 9, 2015 at 7:45 am

#1803954
RE: Ranking duplicate contacts using multiple columns for ranking. Phone and emails that are blank can match to phones and emails that are not blank.

@jeff, please can you amend your sample data set so that it contains each of the conditions you describe? Thanks. Then run this and have a look at the results....
^{“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

June 9, 2015 at 7:04 am

#1803935
RE: Comparing 2 table values

Barcelona10 (6/8/2015)
...Col1 will have value that application will generate(id)....
SQL Server has the IDENTITY attribute and Sequences for this - why reinvent the wheel?
^{“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

June 9, 2015 at 5:22 am

#1803901
RE: Return data between two dates in the past

SandyTucker (6/6/2015)
That worked! Actually I had something like that earlier but didn't realize I still needed the =0 at the end.
Thank you so much both of you, I can't...
^{“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

June 9, 2015 at 4:55 am

#1803897
RE: How to concert alphanumeric with symbols to INT

This is a slightly different take on Dwain's method. I'd guess they perform about the same.
WITH SampleData (NAME, D1, D2, D3, D31) AS
(
SELECT 'X1','9H','30M',NULL,'10M'
...
^{“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

June 8, 2015 at 7:38 am

#1803681
RE: Today's Random Word!

crookj (6/5/2015)
Ed Wagner (6/5/2015)
djj (6/5/2015)
DonlSimpson (6/4/2015)
Ed Wagner (6/4/2015)
Revenant (6/4/2015)
Ed Wagner (6/4/2015)
djj (6/4/2015)
Woolly
Worm
Hole
Security
Clearance
Headroom
Max
Min
nie mouse
^{“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

June 5, 2015 at 6:36 am

#1803274
RE: SQL query

spectra (6/4/2015)
Ok. not an issue. Is it possible to write this in pure SQL way ? (so that I don't face database migration issue later)
My query works...
^{“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

June 4, 2015 at 8:57 am

#1803038
RE: A T-sql question.

Dan121 (6/4/2015)
All I am asking is simple questions. Please forget what I am trying to accomplish or what I am trying get.
After running below query (AdventureWorks database). I get...
^{“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

June 4, 2015 at 8:51 am

#1803031
RE: SQL query

spectra (6/4/2015)
Dear @chrism-2@Work
I had
.....from student_dtl order by intDtlId desc)
did you miss this ?
I wanted to join with the latest record of every profile in this table.
In SQL...
^{“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

June 4, 2015 at 8:50 am

#1803030
RE: An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

CASE
^{“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

June 4, 2015 at 8:26 am

#1803014
RE: A T-sql question.

Jason A. Long (6/4/2015)
ChrisM@Work (6/4/2015)
Jason A. Long (6/4/2015)
Edit... I didn't like the solution I posted...
Solution to what? 😀
It was just a rewrite of the original code Sales.Customer code.
I didn't like...
^{“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

June 4, 2015 at 8:15 am

#1803004
RE: SQL query

spectra (6/4/2015)
Could you please tell how do I optimize this SQL query ? ( I am using MySql)
select DU.kUserId, DU.kUserName , DU.email,DU.contactNo,SID.intDtlId,SID.cbFlag,SID.pcbt,G1.gName as gen1,G2.gName as gen2 from (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

June 4, 2015 at 7:18 am

#1802976
RE: A T-sql question.

Jason A. Long (6/4/2015)
Edit... I didn't like the solution I posted...
Solution to what? 😀
^{“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

June 4, 2015 at 7:06 am

#1802967
RE: Avoid LEFT join

mohanaprabhu.v (6/4/2015)
Chris any update?
ChrisM@Work (6/4/2015)
...
Sure. Start a new thread, and include the Actual Execution Plan as an attachment.
If you want all columns from your invoice table, your options for tuning...
^{“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

June 4, 2015 at 5:13 am

#1802943
RE: Rewriting the Sql code

Jeff Moden (6/3/2015)
Alvin Ramard (6/3/2015)
ChrisM@home (6/3/2015)
GilaMonster (6/3/2015)
What, other than the NOLOCK hints, is the problem?
The OP is confused by the location of the ON clauses. Since there are only INNER...
^{“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

June 4, 2015 at 3:55 am

#1802924

Viewing 15 posts - 2,461 through 2,475 (of 10,143 total)