ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,691 through 6,705 (of 10,143 total)

RE: SUM relative to row date value

Cadavre (8/31/2011)
greg.bull (8/31/2011)
On to the next point, if a month row is missing, then it'd be ignored, so for month 6, we'd be summing month 6, month 5 (which'd 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

August 31, 2011 at 9:29 am

#1375554
RE: Extracting a date from a varchar field

How about posting a dozen or so of these 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

August 31, 2011 at 6:37 am

#1375431
RE: SUM relative to row date value

UPDATE #Sample SET Metric2 = ISNULL(NULLIF(Metric1+Metric1-1+Metric1-2,0),1)
😉
^{“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

August 31, 2011 at 4:29 am

#1375378
RE: SUM relative to row date value

What would be the value for month 5? Is the value 9 correct for month 4?
DROP TABLE #Sample
CREATE TABLE #Sample (Yearmonth DATE, Metric1 INT, Metric2 INT)
INSERT INTO #Sample (Yearmonth, Metric1,...
^{“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

August 31, 2011 at 3:47 am

#1375362
RE: CONCATENATE not working

Probably like this
SELECT LEFT(LTRIM(RTRIM(SUBSTRING(ISNULL(prefix,''),7,4) + phone)),20)
FROM (
SELECT Prefix = ' 0151', phone = '7217164' UNION ALL
SELECT NULL, '7217164'
) [sample]
but it depends on 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

August 30, 2011 at 10:05 am

#1375107
RE: CONCATENATE not working

Looks like Excel. Try this:
LEFT(LTRIM(RTRIM(SUBSTRING(prefix,7,4) + phone)),20)
^{“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

August 30, 2011 at 8:06 am

#1375023
RE: sql query takes long time to execute

Functions around columns in table dbo.PolicyEntry are causing table scans:
replace(executive1,' ','') = 'DINESHANJARA'
Change these to
[executive1] IN ('DINESHANJARA','DINESH ANJARA').
^{“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

August 30, 2011 at 7:35 am

#1374991
RE: Convert Rows Into Column

;WITH SourceTable AS (
SELECT Id = 1, Projectid = 'xxx', MonHour = 8, TueHour = 6, WedHour = 4, ThHour = 6, FriHour = 5, SatHour = 3 UNION...
^{“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

August 30, 2011 at 5:20 am

#1374891
RE: Better Query instead of loop

Job's a good 'un. Thanks for the 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

August 30, 2011 at 4:37 am

#1374878
RE: sql query takes long time to execute

Please post the actual plans, these are estimated plans.
^{“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

August 30, 2011 at 3:04 am

#1374850
RE: Converting a cursor to a non-loop query

DROP TABLE #Application
CREATE TABLE #Application (BoxID INT, Province VARCHAR(10), BoxPosition INT)
INSERT INTO #Application (BoxID, Province, BoxPosition)
SELECT 5632, 'KZN', 1 UNION ALL
SELECT 5632, 'KZN', 2 UNION ALL
SELECT 5632, 'KZN', 3 UNION...
^{“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

August 26, 2011 at 8:54 am

#1373821
RE: Today's Random Word!

Ray K (8/26/2011)
Irene
Polystyrene
^{“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

August 26, 2011 at 7:04 am

#1373742
RE: sql query takes long time to execute

Can you please post the actual plan, and the view definition for ViewEntryDetails.
Cheers
^{“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

August 26, 2011 at 4:27 am

#1373644
RE: Better Query instead of loop

tfifield (8/24/2011)
ChrisM@Work (8/23/2011)
Put it into a stored procedure. Crystal works fine with them, the performance is likely to be better, and there will be no temptation for folks to attempt...
^{“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

August 25, 2011 at 1:53 am

#1372976
RE: Better Query instead of loop

Put it into a stored procedure. Crystal works fine with them, the performance is likely to be better, and there will be no temptation for folks to attempt to do...
^{“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

August 23, 2011 at 10:31 am

#1372144

Viewing 15 posts - 6,691 through 6,705 (of 10,143 total)