See here.

Quite true, but you might benefit from it a lot.

Convert your scalar function to a table-valued function. From BOL:

The APPLY operator allows you to invoke a table-valued function...

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...

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...

Yes but not too early to plan it!

I've got chorizo, some left over roast chicken, red & green peppers and a can of...

Gianluca earns Sainthood points here.

Dindins

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...

I think your profit expression is incorrect:

profit = sum(p.Price - s.Price)

it should be selling price minus purchase price.

No, they work fine together. This[/url] article shows how to use CASE with aggregation to perform high-speed...

In addition to the key points raised by Gianluca, consider also the performance implications of putting this into a WHERE clause.

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...

Paul White NZ (11/1/2010)

---

SQLCLR,...

Paul White NZ (11/1/2010)

---

That helps - as usual - but it's still a bit slower than the (unmodified) XML...