ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,891 through 1,905 (of 10,143 total)

RE: Help on understanding recursive cte

This code sample might help too:
DECLARE
@data VARCHAR(20) = 'Cap',
@DataToReplace VARCHAR(100) = 'cp',
@ReplacedWithData VARCHAR(100) = 'xy'
;WITH Itally AS (SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7)) d (n))
SELECT @data = REPLACE(@Data,SUBSTRING(@DataToReplace,n,1),SUBSTRING(@ReplacedWithData,n,1))
FROM iTally
WHERE n <=...
^{“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

November 30, 2015 at 8:22 am

#1842891
RE: Date Difference

jkramprakash (11/24/2015)
I checked this query with my data.it is returning the following output.
SELECT last_sign_in_at,
CAST(DATEDIFF(yy, last_sign_in_at, GETDATE()) AS NVARCHAR(4)) + ' years, ' +
CAST(DATEDIFF(m, last_sign_in_at, GETDATE()) AS...
^{“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

November 25, 2015 at 8:08 am

#1842322
RE: Where clause prevents index seek

PHXHoward (11/24/2015)
Absolutely the query should be rewritten but it is TG week and the person is out of the office all week.
It would be useful for me to...
^{“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

November 25, 2015 at 1:31 am

#1842251
RE: String Compare

Scott-144766 (11/24/2015)
From Books Online (bold emphasis mine). Is this your problem?
ASCII (Transact-SQL)
Returns the ASCII code value of the leftmost character of a character expression.
Not really, he was warned of this...
^{“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

November 24, 2015 at 8:31 am

#1842083
RE: delete from table then insert - using a view

Minnu (11/24/2015)
hi Team,
How to create a view with below logic,
-- want to delete from unify table before insert.
DELETE FROM dbo.Unify
GO
INSERT INTO dbo.unify (PS_code, PS_Name)
SELECT PS_code, PS_Name FROM [dbo].[Unify_HHT]
As others 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

November 24, 2015 at 6:58 am

#1842034
RE: Where clause prevents index seek

PHXHoward (11/23/2015)
They are using it to create an export of the column to a text file. The _ + primary key is how they want to label the file...
^{“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

November 24, 2015 at 3:16 am

#1841965
RE: Date Difference

michal.lisinski (11/24/2015)
Hi
Please clarify, you want to return digits "1 year" or "one year"?
Beside of question above ,small example:
SELECT name,
CAST(DATEDIFF(yy, create_date, GETDATE()) AS NVARCHAR(4)) + ' years, ' ...
^{“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

November 24, 2015 at 2:35 am

#1841961
RE: Date Difference

jkramprakash (11/24/2015)
I have the table name of User_Details.It contains the column of User_Last_Login_Date.
User_Last_Login_Date
-------------------------
2015-10-01
2015-11-20
2014-01-12
So i want to calculate the date difference with today date in the form of years,...
^{“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

November 24, 2015 at 2:19 am

#1841956
RE: UPDATE statement help

Jason-299789 (11/19/2015)
Jeff,
Thank you for the feedback on the TOP 100 Percent, I do try and avoid it whenever possible, but I know ORDER BY is not allowed in CTE's without...
^{“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

November 19, 2015 at 7:05 am

#1840974
RE: Please help to resolve issue in SQL Query

Lip turner (11/19/2015)
Can you just use like function?
Select * from table
where description like 'des_kak%'
Always test:
DROP TABLE #SampleData
SELECT *
INTO #SampleData
FROM (VALUES
(1, 'Name_A', CAST('des_kakà' AS NVARCHAR(20))),
(2, 'Name_B', CAST('des_kaka'...
^{“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

November 19, 2015 at 7:02 am

#1840970
RE: UPDATE statement help

Shamelessly nicking Jeff's sample data:
--===== If it exists, drop the test table to make reruns easier in SSMS.
-- This is NOT a part of the solution.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL...
^{“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

November 19, 2015 at 5:41 am

#1840948
RE: String Compare

Use UNICODE() instead of ASCII():
DECLARE @C NVARCHAR(10),@R NVARCHAR(10)
SELECT @C=N'12'
SELECT @R=N'12'
SELECT @C, UNICODE(@C), @R, UNICODE(@R)
-- don't forget that you are only comparing the first character of each 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

November 19, 2015 at 3:41 am

#1840913
RE: Nested loop join operator estimate rows is 10000 in compatibility level 2014.

Is t1 a #temp table?
^{“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

November 17, 2015 at 7:57 am

#1840339
RE: Are the posted questions getting worse?

Brandie Tarvin (11/17/2015)
ChrisM@Work (11/17/2015)
Brandie Tarvin (11/16/2015)
Lynn Pettis (11/16/2015)
Blur
Blur
Blur
The safety word is DBCC TIMEWARP(). @=)
Huh?
^{“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

November 17, 2015 at 7:05 am

#1840314
RE: Are the posted questions getting worse?

From some time yesterday through to the present I've been noticing something a little odd. In the workplace, on the commute home last night (the walk, the tube, the train),...
^{“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

November 17, 2015 at 2:16 am

#1840230

Viewing 15 posts - 1,891 through 1,905 (of 10,143 total)