ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,001 through 6,015 (of 10,143 total)

RE: Find last previous and first next

You're welcome, thanks for the generous feedback 🙂
^{“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 27, 2012 at 8:43 am

#1518558
RE: Today's Random Word!

L' Eomot Inversé (7/26/2012)
ChrisM@Work (7/26/2012)
Tom, carbohydrate chemistry is a massive and important field of study which we barely touched on when I did my bichemistry degree in Aberdeen in 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

July 27, 2012 at 8:22 am

#1518547
RE: Find last previous and first next

GigiMi (7/27/2012)
I need to put everything (values for next and previous) in only one row, if possible.
Luigi
Ah I see, sorry my mistake. Try this:
SELECT l.ID, l.Dateref, n.ID, n.Dateref
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 27, 2012 at 8:04 am

#1518536
RE: Find last previous and first next

GigiMi (7/27/2012)
For example, if I pass the date '2012-07-02 10:00:00'
(more recent than any other record) I'd like to obtain something like this:
PreviousID - PreviousDateRef - NetxID - NextDateRef
25...
^{“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 27, 2012 at 7:48 am

#1518518
RE: Find last previous and first next

GigiMi (7/27/2012)
...I need to obtain values also for the next record in the case it does not exist (for example if i pass a datetime more recent than every record...
^{“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 27, 2012 at 7:32 am

#1518508
RE: Find last previous and first next

Here's a couple of ways:
DROP TABLE #Sample
CREATE TABLE #Sample (ID int, DateRef datetime, GroupID int)
INSERT INTO #Sample (ID, DateRef, GroupID)
SELECT 17, '2012-06-20 10:00:00.000', 463 UNION ALL
SELECT 21, '2012-06-22 12:00:00.000',...
^{“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 27, 2012 at 7:21 am

#1518497
RE: Are the posted questions getting worse?

Heya Pauliepoos, haven't seen you posting for ages!
SQL Kiwi (7/27/2012)
ChrisM@Work (7/27/2012)
I'm still uncomfortable with "in order to handle our longest string (500,000 characters)". In all the time I've been working,...
^{“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 27, 2012 at 6:57 am

#1518468
RE: Is there an joiner much like the 8kSplitter?

You've probably cracked this now Anthony, with Lynn's code.
You could do this with a QU on the output but you'd have a new column with an incrementing string until...
^{“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 27, 2012 at 6:17 am

#1518444
RE: Is there an joiner much like the 8kSplitter?

Lynn Pettis (7/27/2012)
Oh, and the expected results based on the sample data.
Thanks.
Lynn, just out of curiosity - because I don't know and "a developer should always know" - do 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 27, 2012 at 2:46 am

#1518344
RE: Are the posted questions getting worse?

Jeff Moden (7/26/2012)
ChrisM@Work (7/26/2012)
WayneS (7/26/2012)
You'll might find this interesting: http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings
Very interesting. Thanks, Wayne.
I know I enjoyed it. Gave me a chance to try out my new 4 band porkchop...
^{“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 27, 2012 at 2:44 am

#1518343
RE: Is there an joiner much like the 8kSplitter?

anthony.green (7/27/2012)
John's solution just run again took 8mins 8 seconds
Lynn's solution as used in a iTVF took 18 minutes 26 seconds.
How many rows are you working with? It would be...
^{“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 27, 2012 at 2:11 am

#1518333
RE: Are the posted questions getting worse?

WayneS (7/26/2012)
You'll might find this interesting: http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings
Very interesting. Thanks, Wayne.
^{“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 26, 2012 at 8:50 am

#1517994
RE: Is there an joiner much like the 8kSplitter?

CROSSTAB and FOR XML PATH would be the most popular around here, there are loads of examples on ssc. FWIW there's a similar thread here.
^{“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 26, 2012 at 8:29 am

#1517974
RE: Today's Random Word!

L' Eomot Inversé (7/23/2012)
ChrisM@Work (7/23/2012)
SQLRNNR (7/23/2012)
C6H12O6
6-(hydroxymethyl)oxane-2,3,4,5-tetrol
Jason's C6H12O6 is a nasty ambiguous formula. ChrisW's thing is less ambiguous (but still ambiguous).
There's a nasty ambiguous word for it: hexose; but it's...
^{“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 26, 2012 at 8:10 am

#1517959
RE: The Beer Cooler Thread

Eric M Russell (7/26/2012)
Lynn Pettis (7/25/2012)
Eric M Russell (7/25/2012)
I'm not a beer connoisseur and don't care for the small batch gourmet stuff. When I drink beer, I'm either drinking Miller...
^{“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 26, 2012 at 7:34 am

#1517933

Viewing 15 posts - 6,001 through 6,015 (of 10,143 total)