ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,506 through 8,520 (of 10,143 total)

RE: Multi Column Sort

Peso (4/13/2009)
Greate job Chris!
But you too will have to check for mutually exclusive records as with Mr Magoos test data.
EDIT: I tried your algorithm with the sample data provided by...
^{“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 13, 2009 at 10:52 am

#976739
RE: Multi Column Sort

It will work with a more sparse data set than the one provided by Barry too. Here's how it works:
Determine the minimum values for each column
Identify the row which contains...
^{“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 13, 2009 at 3:45 am

#976437
RE: how to increment id value in store procedure if id is primary key

Please post the script for the table, this will help to clarify your requirement.
^{“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 11, 2009 at 2:30 am

#976017
RE: Multi Column Sort

RBarryYoung (4/10/2009)
Nice explanation, Peso.
Of course, in this example you are assuming that the first record is easy to identify, but even that is not necessarily so.
It sounds very...
^{“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 10, 2009 at 6:24 am

#975702
RE: Are the posted questions getting worse?

Steve Jones - Editor (4/8/2009)
I'm not sure Jeff will get that one done, so someone else feel free to tackle it. He's agreed to start a nice T-SQL book 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

April 10, 2009 at 2:35 am

#975609
RE: SQL 2000 Query to select the nth row from a Table

arun_anand09 (4/9/2009)
Yes sorry I forgot to mention I have a UNIQUEID Column which starts from 1 and I need to pick 13th row based on this Unique ID.
Whats 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

April 9, 2009 at 10:14 am

#975195
RE: Generating a table with records with all possible values from frequency and interval columns

Hey Flo that was quick:-P
DECLARE @Reference_number VARCHAR(6),
@Amount MONEY,
@Start_payment_date DATETIME,
@interval_type CHAR(1),
@interval_unit SMALLINT, @sql VARCHAR(200)
SELECT @Reference_number = '100001',
@Amount = 100.0,
@Start_payment_date = '15 may 2009',
@interval_type = 'm', --(monthly)
@interval_unit = 1
SELECT @Reference_number AS [Reference_number],...
^{“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 9, 2009 at 9:44 am

#975171
RE: Possible invalid data?

ben.rosato (4/9/2009)
Well, that'll learn me. This was sent to me by someone else and I didn't really verify if the 2 CASTS actually fixed it even though he said...
^{“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 9, 2009 at 8:25 am

#975085
RE: Possible invalid data?

Next:
A.[vchUser3] in (103235, 103236)
toA.[vchUser3] in ('103235', '103236')
^{“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 9, 2009 at 8:13 am

#975074
RE: Possible invalid data?

Then a brief process of elimination:
SELECT E.[IncidentID], CAST(E.[IncidentID] AS NVARCHAR(25))
FROM store.dbo.orders E
WHERE CAST(E.[IncidentID] AS NVARCHAR(25)) LIKE '%.%'
Edit to add comment: This is the only column which doesn't have an "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

April 9, 2009 at 8:04 am

#975065
RE: Possible invalid data?

ben.rosato (4/9/2009)
If you change the last join to the following it works:
Inner JOIN store.dbo.orders E
ON CAST(A.[iSystemId] AS NVARCHAR(25)) = CAST(E.[IncidentID] AS NVARCHAR(25))
Both of those colums are indeed int
Try this: 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

April 9, 2009 at 7:54 am

#975053
RE: Trimming unwanted characters from string

You're totally right Flo, it's overkill, all you need is patindex. It was just for fun.
Try this:
ISNULL(NULLIF(PATINDEX('%[^A-Z]%', NameTrim),0), LEN(NameTrim))
which means: if patindex returns 0, replace it with null, so 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 9, 2009 at 7:44 am

#975042
RE: Avoiding cursors

Scott (4/9/2009)
I have a two column table:
TRX_CODE TRXID
AAA1 19212312
AAA1 ...
^{“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 9, 2009 at 7:38 am

#975032
RE: How to sort alphanumeric values

manohar (4/9/2009)
Arun....
Ur logic works only in the case of alphanumeric values... I throws error in case of string
The error is
------
Msg 245, Level 16, State 1, Line 17
Conversion failed 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

April 9, 2009 at 7:32 am

#975023
RE: Difference between two integer format number

Thishave , after 3 characterwould indicate a VARCHAR or CHAR datatype. Check the datatypes of the columns in the tables. Try casting to INT or BIGINT the column which has...
^{“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 9, 2009 at 7:11 am

#975010

Viewing 15 posts - 8,506 through 8,520 (of 10,143 total)