ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,616 through 3,630 (of 10,143 total)

RE: Alter query change columns

This will work with your "sample" data.
-- sample data
DROP TABLE #Whatnot
CREATE TABLE #Whatnot (company_name int, product_id int, product_name varchar(10))
INSERT INTO #Whatnot VALUES
(1,1,'oi'),
(1,1,'oi2'),
(1,2,'teste'),
(2,1,'oi3')
-- solution
SELECT
company_name,
col1 = MAX(CASE WHEN...
^{“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

February 21, 2014 at 6:48 am

#1691075
RE: Are the posted questions getting worse?

rodjkidd (2/21/2014)
ChrisM@Work (2/21/2014)
SQLRNNR (2/20/2014)
ChrisM@Work (2/20/2014)
SQLRNNR (2/20/2014)
Sean Lange (2/20/2014)
stormsentinelcammy (2/20/2014)
will this forum thread ever get back on topic?
What topic is that???
SQUIRREL
NUTS! WHOLE HAZELNUTS!! Careful, this could go downhill very quickly...
You still...
^{“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

February 21, 2014 at 5:30 am

#1691062
RE: Are the posted questions getting worse?

GilaMonster (2/20/2014)
PSA: Yes, my blogs (and email, etc) is down, I have noticed (no email for almost a week is a good hint). Yes, it is being looked into. ETA...
^{“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

February 21, 2014 at 3:42 am

#1691036
RE: Are the posted questions getting worse?

SQLRNNR (2/20/2014)
ChrisM@Work (2/20/2014)
SQLRNNR (2/20/2014)
Sean Lange (2/20/2014)
stormsentinelcammy (2/20/2014)
will this forum thread ever get back on topic?
What topic is that???
SQUIRREL
NUTS! WHOLE HAZELNUTS!! Careful, this could go downhill very quickly...
You still work 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

February 21, 2014 at 3:40 am

#1691035
RE: Can I use table s in a where statement to get the expected result set?

mattech06 (2/21/2014)
Hey that seems to work on my test data Mitesh (I'll try it on the live stuff in a mo).
One question tho, why did you add?
AND (r.dbStaffLastName IS NULL...
^{“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

February 21, 2014 at 3:08 am

#1691026
RE: Can I use table s in a where statement to get the expected result set?

mattech06 (2/21/2014)
good point, but how do I join them?
How can you possibly code against these tables without knowing what the relationships are between them?
1. Ask your DBA
2. Study 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

February 21, 2014 at 2:52 am

#1691021
RE: Can I use table s in a where statement to get the expected result set?

Your query employs a scary mix of old-style and new-style joins which can be very confusing. There's some evidence for this - you've omitted the join between two of 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

February 21, 2014 at 2:00 am

#1691007
RE: Are the posted questions getting worse?

SQLRNNR (2/20/2014)
Sean Lange (2/20/2014)
stormsentinelcammy (2/20/2014)
will this forum thread ever get back on topic?
What topic is that???
SQUIRREL
NUTS! WHOLE HAZELNUTS!! Careful, this could go downhill very quickly...
^{“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

February 20, 2014 at 9:49 am

#1690779
RE: UPDATE SQL Query Help !

Luis Cazares (2/20/2014)
sqldba20 (2/20/2014)
Nevermind...I was able to figure out using the link posted by Luis. Came with this SQL and it works.
How on earth did you figure that out 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

February 20, 2014 at 9:46 am

#1690772
RE: UPDATE SQL Query Help !

sqldba20 (2/20/2014)
It is only for display purpose in reporting because the user doesn't want to see 4 rows for each record if there are 4 email ids for that AID.
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

February 20, 2014 at 8:53 am

#1690734
RE: Merge two tables into one.

serg-52 (2/20/2014)
Then JOIN them
select x.R1,x.R2,x.R3, y.C1,y.C2,y.C3
from Database.table1 as x
join Database.table2 as y
to get all possible combinations aka cartesian product.
Restrict combinations using WHERE clause
http://technet.microsoft.com/en-us/library/ms191517(v=sql.105).aspx
On its own, join will raise an...
^{“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

February 20, 2014 at 5:49 am

#1690637
RE: Issue with getting phone number for primary and secondary owners of an account

Thanks for posting readily-consumable data, great job. I prefer working with #temp tables and it only took moments to convert your script.
Figuring out how to filter unwanted rows in this...
^{“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

February 20, 2014 at 3:11 am

#1690582
RE: Are the posted questions getting worse?

SQLRNNR (2/19/2014)
Yay - I'm finally insane!!! :hehe::w00t:
Most of those points were insanely well earned, congratulations Jason.
^{“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

February 20, 2014 at 1:53 am

#1690564
RE: Simple query failing

ElijahE (2/19/2014)
Yea, I checked that, and it's still set as GO
ChrisM@Work (2/19/2014)
Have you tried a proper simple query to see if it's the batch separator causing the problem?
SELECT 1
GO
SELECT 1
GO
^{“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

February 19, 2014 at 8:53 am

#1690337
RE: Simple query failing

ElijahE (2/19/2014)
I have SQL 2008r2 installed on my laptop and yesterday I installed SQL 2012. Since doing that the following simple query in SQL 2008r2 fails:
IF OBJECT_ID('usp_test','P') IS NULL
...
^{“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

February 19, 2014 at 8:05 am

#1690297

Viewing 15 posts - 3,616 through 3,630 (of 10,143 total)