ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,826 through 6,840 (of 10,143 total)

RE: Help Needed for SQL Query

Hi Yatish
Thanks for providing more sample data. Here's a modified version of the same query which deals with it:
;WITH Calculator1 AS (
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

July 20, 2011 at 4:25 am

#1355455
RE: Help Needed for SQL Query

Hello Yatish
This works. The principle is the same as the previous version.
CREATE TABLE #History(
HistoryID INT,
Id INT,
letterId INT,
Letter VARCHAR(2),
HistoryRank INT)
INSERT INTO #History VALUES (1101795,265805,5,'NW',11)
INSERT INTO #History VALUES (1119363,265805,5,'NW',10)
INSERT INTO...
^{“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

July 20, 2011 at 2:37 am

#1355421
RE: How to add "dot" after every three digits in a number in sql 2005

Eric M Russell (7/19/2011)
If the length of the number is within a fixed range, like a phone number or IP address, then perhaps the transform could be as simple 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

July 19, 2011 at 9:31 am

#1354907
RE: How to add "dot" after every three digits in a number in sql 2005

Jeff Moden (7/18/2011)
~~I'm just double checking here... has anyone come up with a solution that actually works for this?~~
Scratch that. I see that R. Brush has one.
+2
^{“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

July 19, 2011 at 2:07 am

#1354641
RE: How to add "dot" after every three digits in a number in sql 2005

barsuk (7/15/2011)
Declare @FileName varchar (100)
Set @FileName ='12304208483483'
SELECT REPLACE(Left(Convert(Varchar,Convert(Money,@FileName),1),Len(Convert(Varchar,Convert(Money,@FileName),1)) - 3),',','.')
Try '12304208348300'
^{“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

July 18, 2011 at 2:06 am

#1354077
RE: MAX vs TOP question

Ninja's_RGR'us (7/14/2011)
ChrisM@Work (7/14/2011)
Ninja's_RGR'us (7/14/2011)
ChrisM@Work (7/14/2011)
You can use a variable in TOP in 2k8, not sure about 2k5:
DECLARE @RowsToReturn INT
SET @RowsToReturn = 50
SELECT TOP (@RowsToReturn) * FROM ...
I'm on 2K5 and...
^{“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

July 14, 2011 at 10:00 am

#1352931
RE: MAX vs TOP question

Ninja's_RGR'us (7/14/2011)
ChrisM@Work (7/14/2011)
You can use a variable in TOP in 2k8, not sure about 2k5:
DECLARE @RowsToReturn INT
SET @RowsToReturn = 50
SELECT TOP (@RowsToReturn) * FROM ...
I'm on 2K5 and I tested...
^{“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

July 14, 2011 at 9:53 am

#1352925
RE: MAX vs TOP question

You can use a variable in TOP in 2k8, not sure about 2k5:
DECLARE @RowsToReturn INT
SET @RowsToReturn = 50
SELECT TOP (@RowsToReturn) * 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

July 14, 2011 at 9:45 am

#1352914
RE: How to add "dot" after every three digits in a number in sql 2005

Tim O'Brien-386385 (7/13/2011)
By far the easiest...convert to money, format, strip decimal point:
Print Reverse(SubString(Reverse(Convert(varchar,Convert(Money,1234567891011),1)),4,99))
Prints 1,234,567,891,011
Neither this solution nor the one above it meet the requirements of the OP, which was for...
^{“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

July 14, 2011 at 3:17 am

#1352648
RE: Pivot or row to column

dva2007 (7/13/2011)
...What is the best way to do this?...
Ensure that your table structure is correct and that it is feasible to obtain the results you expect from the data 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

July 13, 2011 at 5:40 am

#1352077
RE: Do you use Facebook?

Michael Valentine Jones (7/12/2011)
Facebook is a good way to slightly interact with relatives and others that you don't really want to have that much to do with while keeping up...
^{“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

July 13, 2011 at 2:09 am

#1351996
RE: Today's Random Word!

Steve Jones - SSC Editor (7/12/2011)
green
Back in Cambridge for the week. Things are very green here, compared to Denver with its light rain and no humidity.
The UK is at its...
^{“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

July 12, 2011 at 9:46 am

#1351696
RE: Help Needed for SQL Query

You're welcome. Thank you for the feedback. Always be sure to vigorously check any code you obtain from any forum.
^{“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

July 12, 2011 at 6:58 am

#1351547
RE: Help Needed for SQL Query

yatish.patil (7/12/2011)
Hi ChrisM@Work,
Thanks for your reply, I did the change and below is the output for the same
...
If you look at the Last two rows for ID 2...
^{“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

July 12, 2011 at 5:24 am

#1351492
RE: Help Needed for SQL Query

yatish.patil (7/12/2011)
HI ChrisM@Work,
Thank you for your query Solution, but when I tested the output for the data as below.
...
You can see the difference between both. Thank you for...
^{“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

July 12, 2011 at 4:54 am

#1351487

Viewing 15 posts - 6,826 through 6,840 (of 10,143 total)