ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,866 through 2,880 (of 10,143 total)

RE: User Defined Function that returns Start Date and End Date

ganteng1 (10/15/2014)
Thanks for that. I tried the following code below but it returned the wrong answer. Do you have any further suggestions:
declare @Month varchar = '02'
declare @Year varchar = '2014'
select...
^{“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

October 15, 2014 at 6:21 am

#1752500
RE: Getting different results!

Not all string functions work with TEXT datatype (http://msdn.microsoft.com/en-GB/library/ms187993.aspx). Try casting to VARCHAR(MAX) before using string manipulation on the column.
^{“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

October 15, 2014 at 6:04 am

#1752489
RE: Improving Index Seek

UDBA (10/13/2014)
...Query is not timing out, it is using the execution plan i attached to the original post but it takes about 10 minutes to run...
Grant isn't talking about 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

October 14, 2014 at 2:30 am

#1752135
RE: Cannot nest inline table functions - bug or bad code?

The syntax of your query might work for scalar functions, but it's incorrect for table-valued functions. Try this instead:
SELECT *
FROM dbo.FN_REPLACE('Testing string', 'Test', '') x1
CROSS APPLY dbo.FN_REPLACE(x1.replaced_string,'String','')
^{“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

October 10, 2014 at 7:24 am

#1751613
RE: how to think about this problem?

Try COUNT() OVER(PARTITION 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

October 10, 2014 at 7:01 am

#1751599
RE: Show emplty output with 0

You're welcome. Can you post up what you have? There may be improvements / better explanations.
^{“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

October 7, 2014 at 6:08 am

#1750537
RE: building groups out of references / eliminate cross references

reto.eggenberger (10/7/2014)
Thx ChrisM for your CROSS APPLY approach. I tried something like this, but sometimes cross apply is a bit misterious to me ...
Your solution doesn't work, because you can'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

October 7, 2014 at 4:21 am

#1750504
RE: Show emplty output with 0

karthik82.vk (10/7/2014)
Hi Chris,
I cannot create rows for the missing dates or change the date as its not a permitted- one. the data comes from another system and adding or changing...
^{“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

October 7, 2014 at 3:32 am

#1750498
RE: building groups out of references / eliminate cross references

DECLARE @tmp TABLE (CustomerID INT, CustomerLink INT, PRIMARY KEY(CustomerID))
INSERT @tmp VALUES
(100001,0),(100002,100001),(100003,100001),(100004,100003),
(100005,100006),(100006,100005),(100007,100006),(100008,0),
(100009,100008),(100010,100013),(100011,100008),(100012,100011),
(100013,100012),(100014,0),(100015,100016),(100016,100015)
;WITH FilledData AS (
SELECT CustomerID, CustomerLink = ISNULL(NULLIF(CustomerLink,0),CustomerID) FROM @tmp
)
SELECT x.GroupID, f.CustomerID, f.CustomerLink
FROM FilledData f
CROSS APPLY (
SELECT GroupID...
^{“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

October 7, 2014 at 2:10 am

#1750483
RE: Show emplty output with 0

karthik82.vk (10/7/2014)
Hi,
I have a sql query that gets the count of exams held in each month. In every month there can be some exams happening or may not happening.
Below is...
^{“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

October 7, 2014 at 1:47 am

#1750481
RE: Improving performance on slow running query

Several alternatives to the query you've posted are available, and posting the execution plan as Gail suggests will probably help to determine which might be best. Here's one of them...
^{“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

October 6, 2014 at 6:49 am

#1750293
RE: Work Pattern Calendar Tables

itwhiz (10/5/2014)
Hi,
I need to prove that there is a speed issue with one of our stored procs/functions ...
Proving that there is a performance issue need not take as much time...
^{“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

October 6, 2014 at 5:32 am

#1750267
RE: Populate a new table column using internal value

pwalter83 (10/6/2014)
ChrisM@Work (10/6/2014)
You need something with which to associate the two tables. Column EDI_PARTNER_CD looks like the only column in MG_EDI_CONVERSION which might be related to COMPANY_ID or COMPANY_NAME.
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

October 6, 2014 at 4:52 am

#1750263
RE: How can i reduce time to generate execution plan and store into cache when Stored procedure execute first time ?

Can you post the actual execution plan for your stored procedure?
^{“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

October 6, 2014 at 4:50 am

#1750262
RE: Populate a new table column using internal value

You need something with which to associate the two tables. Column EDI_PARTNER_CD looks like the only column in MG_EDI_CONVERSION which might be related to COMPANY_ID or COMPANY_NAME.
Do you have...
^{“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

October 6, 2014 at 4:34 am

#1750258

Viewing 15 posts - 2,866 through 2,880 (of 10,143 total)