ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,161 through 5,175 (of 10,143 total)

RE: Case Statement

Continued here.
^{“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

February 27, 2013 at 1:29 am

#1591436
RE: change a result into one record

, x.FAC_ACTIVITY [FAC_ACTIVITY]
, x.NUMMAINTEMP [NUMMAINTEMP]
, LH.dtLeaseFrom [LHdtLeaseFrom]
, LH.dtLeaseTo [LHdtleaseto]
, crp.destimated [crpdestimated]
--, pps.HValue [ppsNSFFEE]
,pps.NSFFee
,pps.MTMFee
,pps.SecDepIntRate
, m.stext [memoStext]
FROM vw_DK_RMMISCDATA x
LEFT JOIN Property p
ON x.RM_ID...
^{“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

February 27, 2013 at 1:28 am

#1591435
RE: change a result into one record

Jeff Moden (2/26/2013)
dwain.c (2/26/2013)
I did have to make an assumption (the 0 in the second row of the set up data) as the OP only provided 4 data columns 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

February 27, 2013 at 1:14 am

#1591427
RE: Case Statement

Ah, I think you're looking at a pivot - does this help?
SELECT
p.HPROP,
p.STYPE,
p.SVALUE,
NSFFee = CASE WHEN p.STYPE = 'nsffee' THEN p.hvalue END,
MTMFee = CASE WHEN p.STYPE = 'MTMFee'...
^{“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

February 26, 2013 at 9:21 am

#1591234
RE: Case Statement

Is this - [SecDepIntRate] a column name or a value?
^{“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

February 26, 2013 at 8:50 am

#1591219
RE: Case Statement

-- APPLY can simplify calculations like this;
SELECT
p.HPROP,
p.STYPE,
p.SVALUE,
x.Newcolumn
FROM PROPOPTIONS P
CROSS APPLY (
SELECT Newcolumn = CASE
WHEN p.STYPE = 'nsffee' THEN 'NSFFee'
WHEN p.STYPE = 'MTMFee' THEN 'MTMFee'
WHEN p.STYPE...
^{“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

February 26, 2013 at 8:15 am

#1591203
RE: procedure performance

It's an estimated plan - the actual plan for one of the queries would be much more informative. However, the following points spring to mind.
Statistics appear to be out of...
^{“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

February 26, 2013 at 7:26 am

#1591185
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

mister.magoo (2/26/2013)
ChrisM@Work (2/26/2013)
dwain.c (2/26/2013)
ChrisM@Work (2/26/2013)
peter-757102 (2/26/2013)
Using a binary comparisons is a good speedup for specific cases and good to be aware of and certainly worth mentioning in bold with...
^{“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

February 26, 2013 at 5:15 am

#1591136
RE: Developer edition availability

T. C. (8/4/2012)
I finally did manage to get hold of a copy - from Grey Matter[/url].
Bought mine from GM too, about fifty quid IIRC. What you actually get is four...
^{“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

February 26, 2013 at 5:10 am

#1591132
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

dwain.c (2/26/2013)
ChrisM@Work (2/26/2013)
peter-757102 (2/26/2013)
Using a binary comparisons is a good speedup for specific cases and good to be aware of and certainly worth mentioning in bold with its benefits....
^{“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

February 26, 2013 at 4:46 am

#1591123
RE: SQL server 2008 query help

BriPan (2/26/2013)
Add alias name to query like C(or any else ) then try ...
That's what I...never mind.
^{“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

February 26, 2013 at 3:55 am

#1591107
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

peter-757102 (2/26/2013)
Using a binary comparisons is a good speedup for specific cases and good to be aware of and certainly worth mentioning in bold with its benefits. What it...
^{“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

February 26, 2013 at 3:53 am

#1591106
RE: Exclude duplicates, keep the value with the latest date

BriPan (2/26/2013)
but If performance is in concern then go for
select CandidateID, max(YourDateColumn)
from YourTable
group by CandidateID
Sean's solution is almost certainly quicker than Kingston's ROW_NUMBER method; however, Kingston's solution is a...
^{“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

February 26, 2013 at 3:48 am

#1591103
RE: Avoid DEADLOCK for concurrent DELETE

VASUforSQL (2/25/2013)
Hi ALL,
I have a table called Products which has many columns.
It is a temporary table used for reporting purpose. The data will be processed to this table concurrently 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

February 26, 2013 at 3:42 am

#1591098
RE: SQL server 2008 query help

mamzy.rahman (2/26/2013)
HI thanks for your help but it came up with an error
incorrect syntax near the keyword 'group'
highlights groub by l01, l03
The derived table - subselect - requires an alias.
^{“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

February 26, 2013 at 3:34 am

#1591093

Viewing 15 posts - 5,161 through 5,175 (of 10,143 total)