ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,361 through 6,375 (of 10,143 total)

RE: paramatize case statement?

Eugene Elutin (4/26/2012)
Without knowing what your tables are and what your full query is, the maximum what I can come up with:
SELECT t.ApprovalID
,u.UserName ...
^{“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

April 26, 2012 at 6:30 am

#1479632
RE: Occurrence count

oolawole (4/25/2012)
Sorry, that does not give the desired output.
Why not post the desired output. All of it, from your sample data set.
^{“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

April 25, 2012 at 7:10 am

#1478942
RE: Occurrence count

oolawole (4/25/2012)
Thanks for that, but it will not solve it. I'll explain again.
For each of the resp_Id, i want a count on each brand (that the resp_Id has) 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

April 25, 2012 at 6:58 am

#1478932
RE: how to join 2 temp table into 1

Ask yourself how you would cope, as a professional SQL Server developer, if you were given the content of your first post as a job specification.
It's awful.
What 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

April 20, 2012 at 4:23 am

#1476512
RE: Today's Random Word!

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

April 5, 2012 at 8:42 am

#1469577
RE: Query find occurence of string

SQL_Surfer (4/4/2012)
I have a comma seperated list of strings like 'abc, efg, ijk, lmn'. ...
In a column of a SQL Server table? Have you tried 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

April 4, 2012 at 7:02 am

#1468799
RE: Today's Random Word!

anthony.green (4/3/2012)
4DayWeek 😀
FollowedByAnother :-))
^{“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

April 3, 2012 at 6:36 am

#1468169
RE: INSERT.....SELECT MAX() problem

What column from which table are you trying to evaluate MAX() of?
Currently you have a "circular" evaluation - the MAX() of something which is never assigned a value.
^{“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

March 26, 2012 at 6:50 am

#1464211
RE: Get month names ordered using recursion

Lisa Slater Nicholls (3/23/2012)
Hi Jeff, I'll have to look into it at home - this gig is 2k5 only 🙁
I did a lot of work on exactly this subject, attempting...
^{“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

March 23, 2012 at 8:03 am

#1463411
RE: Make query Simple

EasyBoy (3/23/2012)
Is there any way i can make below query more simple ?
There are total 5 queries with 5 temp tables. I need to make it simple using just 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

March 23, 2012 at 7:45 am

#1463394
RE: Get month names ordered using recursion

Jeff Moden (3/22/2012)
ChrisM@Work (3/19/2012)
This is pretty fast too:
SELECT n = (n1 + n2 + n3 + n4)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES...
^{“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

March 23, 2012 at 5:45 am

#1463292
RE: Get month names ordered using recursion

This is pretty fast too:
SELECT n = (n1 + n2 + n3 + n4)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS...
^{“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

March 19, 2012 at 7:25 am

#1461116
RE: Get month names ordered using recursion

Jonathan AC Roberts (3/13/2012)
Just to end arguments over performance I tried this:
DECLARE @StartTime datetime
DECLARE @x int
DECLARE @y varchar(20)
DECLARE @MaxIterations int
SET @MaxIterations = 10000
SET @StartTime = GETDATE()
DECLARE @i int
SET @i =...
^{“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

March 13, 2012 at 10:43 am

#1458655
RE: Get month names ordered using recursion

andrew.diniz (3/13/2012)
...Moreover, the recursive CTE solution scales better. Sourcing the parameter list from an underlying table becomes more expensive as the table grows while recursive CTE solution does not. Even...
^{“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

March 13, 2012 at 7:05 am

#1458437
RE: Get month names ordered using recursion

I have to agree with Cadavre here.
It's not just the performance in this case, rCTE's are expensive and shouldn't be used unless they're doing far more work than simply...
^{“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

March 13, 2012 at 4:42 am

#1458381

Viewing 15 posts - 6,361 through 6,375 (of 10,143 total)