ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,371 through 5,385 (of 10,143 total)

RE: Error in max(substring)

DECLARE
@MyInput VARCHAR(8000),
@MyOutput VARCHAR(8000)
SELECT
@MyInput = '101011123',
@MyOutput = ''
;WITH MyTable (startid, Length) AS (
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 4,2 UNION ALL
SELECT 6,3
)...
^{“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 6, 2012 at 2:58 am

#1556373
RE: Error in max(substring)

yuvipoy (11/4/2012)
Say i'm having a string like this '101011123'
i need to split the sting based on my statid & endid
startid, endid
1,1
2,1
3,1
4,2
6,3
which needs to split something like
1,1---->1 starting...
^{“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 5, 2012 at 2:13 am

#1555918
RE: Calling function on select statement

Work on the function in isolation from the rest of the code. It's a rows to columns transformation using a well-known cheat;
SELECT @r = ISNULL(@r+'/', '')
+ DH.DESCRIPTION + '...
^{“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 2, 2012 at 7:42 am

#1555474
RE: Removing unwanted space in the result of SELECT query

Look up RTRIM() in Books Online.
Check the datatype of the column 'name' - is it CHAR()? Sounds like it should be VARCHAR().
^{“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 2, 2012 at 7:29 am

#1555464
RE: Order by is not effective. please provide solution

Materalise the ORDER BY expression in the output to see what's happening:
select top 300 -- widen the range to observe the effect of the ORDER BY
OrderBy = str(isnull(@SortFieldIndex,2)) + @OrderBy,...
^{“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 2, 2012 at 7:23 am

#1555458
RE: Error in max(substring)

yuvipoy (11/1/2012)
can we add
startid, endid SplitLayout
startid, endid
1,1
2,1
3,1
4,2
6,3
9,1
10,5
15,4
19,3
22,4
Startid = will start from 1 and will add endpoint for second row 1+1 =2 will be starting point for second row...
^{“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 1, 2012 at 7:53 am

#1555013
RE: union with order by is not working

28.kanikasoni (11/1/2012)
I had used 5 queries and union their result then appllied order by clause .but order by doesn't work result comes same every time
plz provide solutionnd
Simplify your model. Pick...
^{“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 1, 2012 at 7:52 am

#1555011
RE: quickly parse the query for the problem

Mad-Dog (11/1/2012)
Hi,
is there any quick way to find the problem in this query?
the error is:string or binary data would be truncated
the query:
exec sp_executesql
Why are you using dynamic sql 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

November 1, 2012 at 7:40 am

#1555002
RE: help with this query!!!!

sqldba_newbie (10/31/2012)
ChrisM@Work (10/31/2012)
sqldba_newbie (10/28/2012)
my bad...i have updated the code now...thanks
Where? Without knowing how #sid is populated, it's impossible to do what you are asking.
CREATE TABLE #Sid
...
^{“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 31, 2012 at 1:30 pm

#1554758
RE: Previously well-behaved query is now causing tempdb to choke up

There’s your problem. You’re only updating a maximum of a few tens of thousands of rows in the target table, but the source – now rejigged as a SELECT 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

October 31, 2012 at 9:20 am

#1554655
RE: help with this query!!!!

sqldba_newbie (10/28/2012)
my bad...i have updated the code now...thanks
Where? Without knowing how #sid is populated, it's impossible to do what you are asking.
^{“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 31, 2012 at 8:09 am

#1554613
RE: Previously well-behaved query is now causing tempdb to choke up

How many rows are returned by the SELECT version of the first update in your script?
SELECT PatientKey = P.PatientKey
, AgeKey = PM.AgeKey
, GenderKey = P.GenderKey
, GenderCode =...
^{“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 31, 2012 at 8:03 am

#1554610
RE: Script Needed to pull the data

-- formatted original
select distinct
t1.ServerName,
t1.[Application ID],
t1.[Application Name],
t2.VP1,
T2.VP2,
T2.SVP,
T2.[DC2015 Move Group],
T1.[System Type],
T3.Line,
-- defines 1 column
AppServerSource = (
Select -- returns 3 columns
IsNull(t1.ServerName,t2.ServerName) as ServerName,
IsNull(t1.[Application...
^{“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 30, 2012 at 3:54 am

#1554058
RE: How do I join tables, but select top 1 from 1-many tbl?

chandan.kumar (10/30/2012)
Hi Don,
Can you please check below query -
SELECT c.*, h.*
FROM #CONTACT c
INNER JOIN (
SELECT Accountno, ROW_NUMBER() OVER(PARTITION BY accountno ORDER BY lastdate) Rownum ,RECID FROM #History
GROUP 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 30, 2012 at 3:41 am

#1554052
RE: Return rows with youngest DateTime

jagnew (10/29/2012)
I have a dataset for a ticketing system. The dataset contains the Ticket #, Assigned Technician, and the DateTime they were scheduled on that ticket.
Given this information, for each...
^{“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 29, 2012 at 7:24 am

#1553716

Viewing 15 posts - 5,371 through 5,385 (of 10,143 total)