ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,151 through 3,165 (of 10,143 total)

RE: Splitting time span into multiple entries

DROP TABLE #Sample
CREATE TABLE #Sample (Startdate DATE, [start-time] INT, [end-time] INT, duration INT)
INSERT INTO #Sample (Startdate, [start-time], [end-time], duration) VALUES ('20140710', 820, 1000, 20)
-- How it's done
SELECT *
FROM #Sample s
CROSS...
^{“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 10, 2014 at 9:05 am

#1728428
RE: Are the posted questions getting worse?

Koen Verbeeck (7/10/2014)
Jack Corbett (7/10/2014)
Koen Verbeeck (7/10/2014)
So I tried to endorse Jeff on LinkedIn for BCP, but somehow it doesn't work.
I saw Jeff was endorsed 9 times 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 10, 2014 at 7:45 am

#1728382
RE: Strange behaviour

The query was failing because one or more columns in the original multi-statement table-valued function were non-nullable. It's unlikely that changing the nullability of those columns would change the plan.
^{“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 10, 2014 at 7:38 am

#1728378
RE: Whats wrong with this query?

cstrati (7/10/2014)
select mvID, title, rating, length ,studio
from MovieInfo
where rating not like 'R'
and
where (length > 90)
Seems to be a problem with line 5
Thanks in Advance.
There may be a problem with line...
^{“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 10, 2014 at 5:36 am

#1728337
RE: Strange behaviour

-- You might want to configure that function as an iTVF for performance:
CREATE FUNCTION [dbo].[iTVF_CallbackService_ufn_GetCallerData]
(
@nCaller_NoArgINTEGER,
@cCallerLangArgCHAR(1)
)
RETURNS TABLE
AS
RETURN (
SELECT
[Caller_No]= @nCaller_NoArg,
[Clientname]= RTRIM(LTRIM(c.First_Name)) + ' ' + RTRIM(LTRIM(c.Last_Name)),
[ClientLanguage]= CASE
WHEN @cCallerLangArg = 'F'
THEN...
^{“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 10, 2014 at 5:17 am

#1728322
RE: Strange behaviour

schleep (7/9/2014)
So it turns out that in the 2 failing scenarios, the function is going through the full table before applying the WHERE clause, and failing on a row where...
^{“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 10, 2014 at 4:41 am

#1728312
RE: how to use substring and charindex to extract desired string

SELECT
MyString,
x.p1, y.p2, z.p3,
LeftBitty = LEFT(MyString,NULLIF(z.p3,0)-1)
FROM (VALUES ('ek/df/cv/'), ('ek/df/cv/f'), ('ek/df/cvf')) d (MyString)
CROSS APPLY (SELECT p1 = CHARINDEX('/',MyString,0)) x
CROSS APPLY (SELECT p2 = CHARINDEX('/',MyString,p1+1)) y
CROSS APPLY (SELECT p3 =...
^{“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 10, 2014 at 2:05 am

#1728291
RE: Parsing strings from a field value

Joe Contreras-290946 (7/9/2014)
Hi,
I created an ETL process using C# (SSIS was not available) and ran into some issues with one of the fields.
I have a [description] that 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

July 9, 2014 at 9:40 am

#1728125
RE: Are the posted questions getting worse?

Grant Fritchey (7/9/2014)
Where does my data come from?
Well the mommy data and the daddy data loved each other very much so....
Bwahaaaa! I was so tempted to post "Haven't you had...
^{“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 9, 2014 at 7:04 am

#1728026
RE: How to Consume Return Values From Table Valued Function

INSERT INTO TargetTable (<<columnlist>>)
SELECT <<columnlist>>
FROM SourceTable
^{“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 9, 2014 at 6:23 am

#1728009
RE: How to Consume Return Values From Table Valued Function

Treat it as any other table source: SELECT * FROM dbo.MyFunction(param, param)
Having said that, the rCTE string splitter is known to perform poorly against other methods. You can read about...
^{“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 9, 2014 at 5:55 am

#1727998
RE: set date value based on other row values

This should get you started:
;WITH SequencedData AS (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY [Priority])
FROM #set_dates
)
SELECT
[Split3_ID], [CU_ID], [order_id], [st_date], [sku], [Priority],
o.[Delay],
[CourseDate] = CASE WHEN o.rn =...
^{“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 8, 2014 at 6:09 am

#1727733
RE: Transpose rows into one column

prabhu.st (7/8/2014)
yes, ChrisM's query is simple aswell it is more efficient and Optimized too..
--Estimated Subtree Cost = 0.0034222 (to calculate 21.6 rows)
select Field from #tempunpivot UNPIVOT (Field for ColumnName IN...
^{“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 8, 2014 at 5:41 am

#1727723
RE: Transpose rows into one column

SELECT NewColumn
FROM #test t
CROSS APPLY (VALUES (id_respondent), (projid), (gfkroid)) d (NewColumn)
^{“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 8, 2014 at 4:00 am

#1727689
RE: How to combine two codes

SELECT
a, -- column is in #TableA only
b, -- column is in #TableA only
c, -- column is in #TableA only
d, -- column is in both tables
e =...
^{“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 8, 2014 at 3:17 am

#1727684

Viewing 15 posts - 3,151 through 3,165 (of 10,143 total)