ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,746 through 8,760 (of 10,143 total)

RE: How to find Week start date and Week end date

karthikeyan (3/3/2009)
His method is working fine. I have tested it.
Actually my requiremrnt is little bit different what he has given.
Can you demonstrate please, Karthik? I'd be very keen to...
^{“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 3, 2009 at 8:19 am

#952440
RE: query

If new rows were going into table1 until it was replaced by table2, then this might be more appropriate:
SELECT emp_id, name, CAST(NULL AS CHAR(20)) AS emp_address, DOB
FROM table1
UNION ALL
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

March 3, 2009 at 6:58 am

#952394
RE: How to find Week start date and Week end date

karthikeyan (3/3/2009)
Hi All,
Thanks for all your inputs!:)
I found the TSQL 101 solution.
Declare @InputDate Datetime
select @InputDate = '02/mar/2009'
select case when upper(datename(dw,@InputDate)) = 'MONDAY' then dateadd(dd,-7,@InputDate)
...
^{“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 3, 2009 at 5:07 am

#952336
RE: Help Needed for Update Query

When writing an UPDATE ... FROM ..., it pays to write it as a SELECT first, to see what rows are going to be affected and what the new 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 3, 2009 at 3:59 am

#952305
RE: Removing cursor from TSQL - 1

Jeff Moden (3/2/2009)
There's no special technique for this... it's simply the choice to figure it out using regular ol' set based code. It doesn't require a Tally table or...
^{“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 2, 2009 at 9:34 am

#951933
RE: Removing cursor from TSQL - 1

Here's the same code with the outer loop removed and the update statements
replaced by PRINT statements, to try to identify what this thing does.
I made the assumption that...
^{“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 2, 2009 at 9:32 am

#951931
RE: Split string field based on character position

Here's a quick sample using Jeffs' suggestion of PARSENAME:
DROP TABLE #Sample
CREATE TABLE #Sample (id INT, PersonName VARCHAR(100))
INSERT INTO #Sample (id, PersonName)
SELECT 1,'Doe, John H.' UNION ALL
SELECT 2,'Pardeshi, Mangal' UNION ALL
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

March 2, 2009 at 4:37 am

#951749
RE: Can any one tell me?

Jeff Moden (2/27/2009)
I'd still like to know why we're spelling out dates. What are the business rules that would require such a thing?
Also, just a friendly tip... you don'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

March 2, 2009 at 2:27 am

#951715
RE: One update statement - 2 messages with # rows affected

Check triggers on company_ext_attributes
^{“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 27, 2009 at 8:00 am

#951180
RE: Are the posted questions getting worse?

Jeff Moden (2/27/2009)
Oracle... when code is autogenned, it comes out in all uppercase for things like column names whether you want it to or not. Oracle peeps have gotten...
^{“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 27, 2009 at 6:48 am

#951126
RE: Can any one tell me?

saravanantvr1984 (2/27/2009)
My hearty thanks to Chris Morris
That's nice - you're welcome!
I have to agree with Jeff however, this type of processing is almost always better done by the application/presentation...
^{“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 27, 2009 at 6:42 am

#951119
RE: How to get this Result, Unusual Join

Hi Crazyman
If you look at the result from the sample query
SELECT a.[Date], a.Userid, a.[Name] , '#', b.UseridRef, b.Userid
FROM #Temp a (nolock)
LEFT JOIN #TempCross b(nolock)
ON 'S000'+ CAST(b.UseridRef 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

February 27, 2009 at 6:36 am

#951112
RE: problems using charindex function

Have some coffee - the expressions are the wrong way round in the function 🙂
declare @searchterm nvarchar(200)
SET @searchterm = 'blood and sweat'
SELECT charindex(@SearchTerm,'and'), charindex('and', @SearchTerm)
^{“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 27, 2009 at 4:55 am

#951062
RE: Are the posted questions getting worse?

GilaMonster (2/26/2009)
Jeff Moden (2/26/2009)
All SQL Keywords in UPPER CASE.
All database names, table names, procedure names, column names, column aliases, variable names (basically, my stuff) etc, in MixedCase with no underscores....
^{“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 27, 2009 at 2:20 am

#951032
RE: Are the posted questions getting worse?

Jack Corbett (2/26/2009)
Back to trying to get newbies to read the articles, instead of having a popup or anything, is there a confirmation email that goes out when you register...
^{“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 26, 2009 at 5:32 am

#950524

Viewing 15 posts - 8,746 through 8,760 (of 10,143 total)