ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,506 through 5,520 (of 10,143 total)

RE: TSQL Case Statement help

Roland Alexander STL (10/2/2012)
You're getting an error because the CASE statement needs to be enclosed in parentheses.
I'm unaware of this as a requirement in TSQL.
I think it's more likely because...
^{“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 2, 2012 at 7:18 am

#1544360
RE: TSQL Case Statement help

Select case
When jdt_jty_code IN ('ISCO','ISSP','IECO','IECM','IESP','IEHD','ISHD','FRSI') Then 'Install'
When jdt_jty_code IN ('SB42','SB4W','HD42','HD4W','SA2C','SA2W','HD2C','HD2W','SNCO') Then 'Service Call'
Else 'UNKNOWN' END
^{“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 2, 2012 at 7:04 am

#1544351
RE: how to avoid duplicate business logic

norbert.manyi (10/2/2012)
...Ps. I understand that this particular example can be done using views, but I'm taking about the situations where you cannot do this. (I just can't find a good...
^{“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 2, 2012 at 7:00 am

#1544349
RE: Using MERGE for Audting

If you are using the Enterprise edition of SS2K8, then change data capture is well worth a look.
^{“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 2, 2012 at 5:12 am

#1544312
RE: find increase/decrease amount

-- you have 2 rows for sector 'A', month 1
-- what do you want to do with them?
-- It's not clear from your "Required Output", which...
^{“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 2, 2012 at 4:51 am

#1544300
RE: Are the posted questions getting worse?

HowardW (10/2/2012)
Anyone else eagerly awaiting this?
Have to say, getting increasingly irate about e-book pricing. Why do I have to pay 50p more, given all the costs of printing, distributing and...
^{“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 2, 2012 at 4:11 am

#1544285
RE: Help needed with a query (Cursors+union+pivot?)

gianlud75 (10/2/2012)
...
What I would like to get, is something like the image in attachment
You have to use dynamic sql for this. The core query is this:
SELECT
a.SURV_ID,
[First Question] =...
^{“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 2, 2012 at 4:00 am

#1544283
RE: Why does this take so long!

deepakagarwalathome (10/1/2012)
...There are no indexes on any of the tables...
This might have something to do with it. The first sentence of an excellent indexing article[/url] reads "Indexes are fundamental 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

October 2, 2012 at 3:16 am

#1544269
RE: How to eval @Variable when assigned from SELECT field?

jmccoy-1028380 (10/1/2012)
Yes! That works as desired. Thank You!
Are there other ways though, for evaluating it?
--something like:
IF isnull(@OrderNumRtrn, 0) = 0 OR @OrderNumRtrn = '000000'
...
^{“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 2, 2012 at 2:33 am

#1544255
RE: Cross joining one table to find duplicates

d.aarts (10/2/2012)
I have been working on cleaning a database with addresses in an attempt to dedupe it. There's a lot of pollution and duplicates in the table. Due to efficiency...
^{“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 2, 2012 at 2:15 am

#1544249
RE: How can I count the number of occurances of a string in an ntext column for each record?

Mixolydian (10/1/2012)
Wow! I had hoped to find a response but I am overwhelmed. Thank you all very much!
Lowell, thank you for the valuable tip. I do need to check...
^{“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 2, 2012 at 2:05 am

#1544246
RE: Consolidate Overlapping Date Periods

dwain.c (10/1/2012)
😀
ChrisM@Work (9/28/2012)
dwain.c (9/27/2012)
Phil,
I think this is a pretty snappy query too.
<<snip>>
Chris - I like what you did with the CROSS APPLY and I was able to do that 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

October 1, 2012 at 9:02 am

#1544017
RE: How can I count the number of occurances of a string in an ntext column for each record?

laurie-789651 (10/1/2012)
laurie-789651 (10/1/2012)
This appears to work:
I've elected to show rows where both values are zero in case these are wrong - You can remove this if you want.
I've set MAXRECURSION...
^{“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 1, 2012 at 6:29 am

#1543946
RE: How can I count the number of occurances of a string in an ntext column for each record?

This will get you 1 and 2:
DROP TABLE #tblHTML
CREATE TABLE #tblHTML (RowID INT, strPage ntext)
INSERT INTO #tblHTML (RowID, strPage)
SELECT
1, REPLICATE(CAST('I have a table [tblHTML] with an ntext column [strPage]...
^{“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 1, 2012 at 5:56 am

#1543931
RE: Are the posted questions getting worse?

L' Eomot Inversé (9/28/2012)
ChrisM@Work (9/28/2012)
Koen Verbeeck (9/28/2012)
laurie-789651 (9/28/2012)
Koen Verbeeck (9/28/2012)
Grant Fritchey (9/27/2012)
THREADIZENS!
Who is going to make it to any of the SQL in the City events coming up in 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 1, 2012 at 1:52 am

#1543803

Viewing 15 posts - 5,506 through 5,520 (of 10,143 total)