ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,841 through 6,855 (of 10,143 total)

RE: Help Needed for SQL Query

;WITH Calculator1 AS (
SELECT Historyid, ID, LetterNo, letter,
rn1 = ROW_NUMBER() OVER(ORDER BY Historyid),
rn2 = ROW_NUMBER() OVER(PARTITION BY Letter ORDER BY Historyid)
FROM #Test),
Calculator2 AS (
SELECT Historyid, ID, LetterNo,...
^{“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:41 am

#1351483
RE: Do you use Facebook?

Facebook is awesome for retaining a link with distant relatives. I have cousins in Canada and Hong Kong (and family scattered all around the UK) and FB lets me keep...
^{“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 2:59 am

#1351467
RE: String to Paragraph.

Here's another APPLY solution:
DROP TABLE #Strings
CREATE TABLE #Strings (Longstring VARCHAR(150))
INSERT INTO #Strings (Longstring) values ('SABRE a CIRCUIT BREAKER MOTORISATION SCHEMATIC DIAGRAM, FOR USE WITH MICOM & A MULTI VOLTAGE SHUNT...
^{“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 11, 2011 at 9:45 am

#1351173
RE: Today's Random Word!

Daniel Bowlin (7/11/2011)
falling behind
It's all going South sooner or later.
^{“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 11, 2011 at 8:03 am

#1351086
RE: SQL Update Behaviour

John Mitchell-245523 (7/7/2011)
... the value will be updated with all the values, leaving the one that was done last as the value that persists...
From BOL UPDATE section: "...This is because...
^{“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 7, 2011 at 7:05 am

#1349661
RE: Running total (cummulative Sum)

SQLkiwi (7/7/2011)
... By the way, a windowing function in the recursive part of a CTE has a weird semantic (quite counter-intuitive, in fact) but works the way it does because...
^{“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 7, 2011 at 6:39 am

#1349641
RE: Running total (cummulative Sum)

SQLkiwi (7/7/2011)
Hi Chris,
There's no need for a join at all - a point I realised after editing the TOP trick into your code. I have updated the code 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 7, 2011 at 5:50 am

#1349591
RE: Running total (cummulative Sum)

The new version runs faster but not by much.
So the optimizer converts what looks like a triangular join (the entire CROSS APPLY part) into a TOP query?
Interestingly, you can...
^{“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 7, 2011 at 5:36 am

#1349575
RE: Running total (cummulative Sum)

Nice one, Paul. The triangular join put me off writing and testing it, but in practice the performance is stunningly good - half a million rows return in 12s on...
^{“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 7, 2011 at 4:47 am

#1349548
RE: Running total (cummulative Sum)

Craig Farrell (7/6/2011)
Jeff Moden (7/6/2011)
Chris,
Do you know of a way to use the rCTE to do a running total without the ID's being perfectly sequential?
There's a way using Cross Apply...
^{“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 7, 2011 at 2:04 am

#1349466
RE: Running total (cummulative Sum)

tommey152 (7/6/2011)
Hi ,
.....
Is there any SQL statement that can do this easily? What's the best way to accomplish this?
Easiest is the recursive CTE:
DROP TABLE #SourceData
CREATE TABLE #SourceData (id INT...
^{“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 6, 2011 at 6:57 am

#1348896
RE: where condition parameter

-- simplest
SELECT *
FROM t
WHERE YEAR(CASE
WHEN t.[type] = 'import' THEN t.arrdate
WHEN t.[type] = 'export' THEN t.depdate
ELSE NULL END) = YEAR(@deparr)
-- possibly faster
SELECT *
FROM 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

July 1, 2011 at 9:07 am

#1347079
RE: Today's Random Word!

Today's Random World: "Dragon's Egg".
^{“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 1, 2011 at 8:10 am

#1347037
RE: Join to at least one record as default

SQLkiwi (6/30/2011)
ChrisM@Work (6/30/2011)
...code...
Hi Chris,
You might be interested in a slight tweak to that code. Take a look at the query plans for these:
...
Notice the special distinct sort 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 1, 2011 at 2:05 am

#1346902
RE: Join to at least one record as default

eseosaoregie (6/30/2011)
I have a table which lists finance reporting units (RU), buisness units(BU) and there respective consolidation entity (ENT).
Every RU and BU combo is related to more than 1 ENT....
^{“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

June 30, 2011 at 6:51 am

#1346503

Viewing 15 posts - 6,841 through 6,855 (of 10,143 total)