ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,156 through 7,170 (of 10,143 total)

RE: select statement

MonsterRocks (10/21/2010)
then how to convert a varchar type variable into datetime type?..
Look up CONVERT in BOL, and choose the 'Style' which matches your text date. 106 looks promising even though...
^{“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 21, 2010 at 7:10 am

#1240278
RE: Can any one please guide,how to split this result,

Humble apologies. Revised code attached.
Declare @t Table
(
id int,
EmpName nvarchar(500),
...
^{“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 21, 2010 at 6:56 am

#1240260
RE: select statement

What datatype are the 'date' columns in your 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

October 21, 2010 at 6:38 am

#1240244
RE: Can any one please guide,how to split this result,

sharath.chalamgari (10/21/2010)
Really i didn't understand the solution that is posted here.Please can some one explain me what exactly the Query is doing.
Sure. Here's a start:
-- create a sequence table (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

October 21, 2010 at 5:22 am

#1240191
RE: call a cursor in a procedure

MonsterRocks (10/21/2010)
oh....Thanks a lot.... thanks Chris and gail....
Study the code, if there's anything you are unsure of, then please ask 🙂
^{“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 21, 2010 at 4:50 am

#1240163
RE: call a cursor in a procedure

You don't need a cursor for this, it's a simple UPDATE...FROM:
UPDATE l SET
CAL_HEADER_ID= @PVC_CALENDAR_HEADER_ID,
PERIOD_NAME= t.ATTRIBUTE2, --@lvc_period_name,
FROM_DATE= CONVERT(VARCHAR(11),LTRIM(RTRIM(t.ATTRIBUTE3)), 106), --@ld_from_date_1,
TO_DATE= CONVERT(VARCHAR(11),LTRIM(RTRIM(t.ATTRIBUTE4)), 106), --@ld_to_date_1,
QUARTER_NUM= t.ATTRIBUTE5, --@li_quarter_num,
F_YEAR= t.ATTRIBUTE6, --@lvc_f_year,
ADJUSTING_PERIOD = t.ATTRIBUTE7, --@li_adjusting_period,
TRN= @PBI_TRN,
STATUS=...
^{“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 21, 2010 at 4:42 am

#1240155
RE: Can any one please guide,how to split this result,

SELECT s.*, x.*
FROM @t s
CROSS APPLY (
SELECT EmpNamePart = SUBSTRING(s.EmpName, b, e), b, e
FROM (
SELECT
n.n AS b, -- delimiter at beginning of string
ISNULL(NULLIF(CHARINDEX(',', s.EmpName, n.n+1), 0), LEN(s.EmpName)+1)-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

October 21, 2010 at 4:17 am

#1240140
RE: CASE WHEN in Order By statement

When you wrapped your query in another SELECT (sometimes - usefully - known as an onion select), you removed the DISTINCT. Remember "ORDER BY items must appear in the 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 20, 2010 at 7:48 am

#1239691
RE: CASE WHEN in Order By statement

Just as the message says,
Msg 145, Level 15, State 1, Line 14
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Try this, which works
;WITH TestCTE...
^{“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 20, 2010 at 7:07 am

#1239653
RE: replacing long running cursor

Sanjay-940444 (10/20/2010)
I have one question here
Does SQL Server guarantee that update will be done strictly in top down manner?
I didnt find it writtem anywhere in BOL.
The update is performed strictly...
^{“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 20, 2010 at 6:39 am

#1239633
RE: call a cursor in a procedure

You don't. You DECLARE it, then use it, exactly the same as you might in an SSMS window.
Can you give us a few more details of what it is you...
^{“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 20, 2010 at 5:38 am

#1239591
RE: Where are you?

Tom.Thomson (10/19/2010)
Ray K (10/19/2010)
Boy, this thread's been too quiet lately. Let's stir the pot a bit.
How about . . . neat things to do or places to see...
^{“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 20, 2010 at 5:05 am

#1239581
RE: Today's Random Word!

CirquedeSQLeil (10/19/2010)
predicament
Foreplay
^{“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 20, 2010 at 2:52 am

#1239527
RE: T-SQL for getting string function

mpalaparthi (10/18/2010)
Hello SQL Experts,
I have a question.... I have 3 tables (table1, table 2 and table3)
table1: ...
^{“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 19, 2010 at 6:39 am

#1238997
RE: Today's Random Word!

sing4you (10/18/2010)
...I knew I was dating myself with that one...
The upside to multiple personality disorder?
^{“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 19, 2010 at 2:34 am

#1238887

Viewing 15 posts - 7,156 through 7,170 (of 10,143 total)