ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,111 through 7,125 (of 10,143 total)

RE: CROSS APPLY and Invalid Object Name

SwePeso (11/3/2010)
You don't need a tvf here.
Quite true, but you might benefit from it a lot.
^{“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 3, 2010 at 6:34 am

#1245945
RE: CROSS APPLY and Invalid Object Name

Convert your scalar function to a table-valued function. From BOL:
The APPLY operator allows you to invoke a table-valued function...
^{“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 3, 2010 at 6:19 am

#1245941
RE: Please help with a query

If the column 'side' indicates Buy or Sell, then the WHERE clauses for the two CTE's should be swapped around, from
;WITH Purchases AS
(-- get all of the purchases. Cross Join...
^{“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, 2010 at 9:50 am

#1245508
RE: Please help with a query

keymoo (11/2/2010)
Profit should be the sale price - purchase price. Have a look at tradeId 4.
Buy 1 unit at 1.3641
Buy 1 unit at 1.3597
Sell 2 units at 1.3594
The profit should...
^{“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, 2010 at 9:26 am

#1245492
RE: Today's Random Word!

Tom.Thomson (11/2/2010)
Chris Morris-439714 (11/2/2010)
Dindins
A bit early for that?
Yes but not too early to plan it!
I've got chorizo, some left over roast chicken, red & green peppers and a can 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

November 2, 2010 at 8:52 am

#1245451
RE: Are the posted questions getting worse?

Gianluca earns Sainthood points 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

November 2, 2010 at 8:17 am

#1245410
RE: Today's Random Word!

Dindins
^{“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, 2010 at 7:46 am

#1245366
RE: Please help with a query

The calculation is working as expected. Using your sample data above, you have a purchase price of 1.254000 and a sale price of 1.252700. That's a loss as you point...
^{“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, 2010 at 7:45 am

#1245363
RE: Please help with a query

I think your profit expression is incorrect:
profit = sum(p.Price - s.Price)
it should be selling price minus purchase price.
^{“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, 2010 at 7:19 am

#1245337
RE: Case statement won't work in grouping

Sacheen (11/2/2010)
Yep.. it worked fine.. 😀
seems like grouping won't work with case statements...! :unsure:
No, they work fine together. This[/url] article shows how to use CASE with aggregation to perform high-speed...
^{“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, 2010 at 6:41 am

#1245312
RE: Dynamic sql in functions

In addition to the key points raised by Gianluca, consider also the performance implications of putting this into a WHERE clause.
^{“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, 2010 at 4:53 am

#1245271
RE: Calculating interest query

Ninja's_RGR'us (11/1/2010)
Chris Morris-439714 (11/1/2010)
Paul White NZ (11/1/2010)
Chris Morris-439714 (11/1/2010)
Nah mate my walking-on-water skills are still underwhelming - don't even drink the stuff, fish f...never mind. Spill the beans! I'll try...
^{“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, 2010 at 8:46 am

#1244864
RE: Calculating interest query

Paul White NZ (11/1/2010)
Chris Morris-439714 (11/1/2010)
Nah mate my walking-on-water skills are still underwhelming - don't even drink the stuff, fish f...never mind. Spill the beans! I'll try to understand, promise!
SQLCLR,...
^{“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, 2010 at 7:47 am

#1244825
RE: Calculating interest query

Paul White NZ (11/1/2010)
Chris Morris-439714 (11/1/2010)
Heh - until you wrap it up nice and warm...
That helps - as usual - but it's still a bit slower than the (unmodified) XML...
^{“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, 2010 at 7:39 am

#1244819
RE: Calculating interest query

Jeff Moden (11/1/2010)
Damn! There is a trade off. If you don't know there's going to be dupes or not, pray for dupes when you use it. 🙂
USE...
^{“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, 2010 at 7:18 am

#1244804

Viewing 15 posts - 7,111 through 7,125 (of 10,143 total)