ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,261 through 7,275 (of 10,143 total)

RE: Today's Random Word!

Ray K (9/29/2010)
tosscrosby (9/29/2010)
Ray K (9/29/2010)
Cat
(as in missing -- he didn't come home last night, which is not like him. Mommy and Daddy are worried.)
Did you do...
^{“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

September 29, 2010 at 6:55 am

#1228962
RE: need help in having clause

Here's another way, less cluttered. As always, check performance before deciding which solution to use.
; WITH PartitionedData AS (
SELECT ElementCount = COUNT(*) OVER(PARTITION BY column1, column2, column3),
* --...
^{“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

September 29, 2010 at 6:49 am

#1228956
RE: Similar groups placed randomly must be allocated a group number each

Here's the other thread we were all thinking of:
http://www.sqlservercentral.com/Forums/FindPost931414.aspx
^{“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

September 29, 2010 at 5:52 am

#1228912
RE: Similar groups placed randomly must be allocated a group number each

ColdCoffee (9/29/2010)
...And Chris, your solution actually numbers sequentially each row in a group, resetting the counter and starting from 1 for the next... That's not specifically what i am after...
^{“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

September 29, 2010 at 5:01 am

#1228894
RE: Query Performance tuning

Have you tried using CTE's and ROW_NUMBER() for this?
;WITH PartitionedData AS
(SELECT secid, marketCap, tradeDate,
RowID = ROW_NUMBER() OVER(PARTITION BY secid ORDER BY tradeDate DESC)
FROM SecMarketCap
)
SELECT a.secid,
a.tradeDate,
a.marketCap/b.marketCap - 1
FROM PartitionedData...
^{“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

September 29, 2010 at 3:47 am

#1228842
RE: Similar groups placed randomly must be allocated a group number each

Hi ColdCoffee
Funny - I've had to do this recently in a production system but I couldn't find the code or even remember the method. Age sucks.
Here's something which works...
^{“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

September 29, 2010 at 3:04 am

#1228824
RE: Today's Random Word!

bcsims 90437 (9/28/2010)
John Rowan (9/28/2010)
Ice
Ice Cream. It's HOT.
Baked Alaska
^{“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

September 29, 2010 at 2:22 am

#1228809
RE: Are the posted questions getting worse?

Tom.Thomson (9/28/2010)
...I would say that as a general rule, letting a "business analyst" anywhere near anything you seriosly want to happen is a big mistake. A person who can...
^{“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

September 29, 2010 at 2:17 am

#1228805
RE: update statement not working in stored proc

Alter this to match your object names, then run it.
If you are experiencing the same problem then it's probably a SP settings issue, check datatype of Col1.
UPDATE dbo.XXX
SET Has_Errors =...
^{“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

September 28, 2010 at 10:13 am

#1228506
RE: Today's Random Word!

Ray K (9/28/2010)
Eschew obfuscation
You what????
^{“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

September 28, 2010 at 9:53 am

#1228495
RE: Group By help to remove duplicates.

Sounds like this to me:
;WITH MyStructuredData AS (
SELECT RowID = ROW_NUMBER() OVER(PARTITION BY PolicyKey, PaymentAmountOriginalCurrency
ORDER BY PolicyKey, PaymentAmountOriginalCurrency, accountspaymentkey),
PolicyKey, PaymentAmountOriginalCurrency, accountspaymentkey
FROM MyTable
)
SELECT
pos.PolicyKey,
pos.accountspaymentkey,
neg.accountspaymentkey,
pos.PaymentAmountOriginalCurrency,
neg.PaymentAmountOriginalCurrency
FROM...
^{“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

September 28, 2010 at 9:32 am

#1228476
RE: Today's Random Word!

Paris Hilton
^{“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

September 28, 2010 at 7:19 am

#1228322
RE: Return string value when fields are null

byrdmom2 (9/28/2010)
You're right! No need for the table variables. Your query works fine and I will replace mine. Is there a way to change the color when I replace 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

September 28, 2010 at 7:00 am

#1228300
RE: Return string value when fields are null

--***get the information from both tables and concatenate date and city
select
t1.eForm53_GUID,
t1.fullname,
convert(varchar(10), b, 101) as RecCreatedDate,
(select convert(varchar(10), b, 101) + ' ' + c as 'data()'
--from...
^{“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

September 28, 2010 at 6:53 am

#1228294
RE: Return string value when fields are null

Why are you pulling results from your two source tables into table variables before using them in your final query? This is a shedload of extra code (trivial) and could...
^{“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

September 28, 2010 at 6:44 am

#1228290

Viewing 15 posts - 7,261 through 7,275 (of 10,143 total)