Awesome Lutz, thanks so much for your help once again! Perfect, elegant solution and thanks for the index tip too.

Hi Lutz,

The expected result is

MONTH startBalance endBalance pctChange

============== ============ ==========...

I see, in that case there is not a 1:1 relationship between Intruments table and WhateverInstruments tables but a 0-1:1 one which makes things a little different. I can live...

Because BondInstrument...

I have a couple of volunteers for this now, thanks very much for looking.

That's great thanks a lot, I have amended it a little to cope with short trades by including a CASE statement. Thanks for your time.

The Dixie Flatline (6/6/2011)

---

It's not perfect....

Thanks, I'm wondering if I should be doing this in the application layer in C#. :-/ However it makes sense to me to do calculations on sets of data close...

Wow CELKO, what a great reply, thanks! I learnt a lot from this. You asked for an example, well I gave one with trade_id 182. I can walk through it...

Please ignore this, I hadn't realised that a logical definition of a constraint is bound to the physical manifestation (an index). It seems that dropping and recreating all indexes is...

Hi Craig,

I basically need to transpose columns to rows. So instead of the results looking like this:

largestDrawDownPct avgPctAccountChange maxPctAccountChange minPctAccountChange avgPctAccountChangeWinners avgPctAccountChangeLosers

------------------ ------------------- ------------------- ------------------- -------------------------- -------------------------

-3.49 ...

Jeff Moden

Just to follow up on that... a good ol' fashioned cross-tab can make life real easy and "prettier".

SELECT Symbol,

SUM(CASE WHEN...

Sorry for the tardiness - here's some sample data and the actual query I am running:

set nocount on

CREATE TABLE #BaseTradeStats(

[tradeId] [int] NOT NULL,

[minOrderDateTime] datetime,

[symbol] [char](6) NOT NULL,

[pctAccountChange] [decimal](38, 6) NULL,

...

You might want to do a performance test or analyse the query plan, but my feeling is that the multi-proc approach would be better for the optimizer, and would not...

Thanks, could you post...

Thanks Wayne, the problematic part is getting the XML data from the internet. I tried this query and it doesn't work:

select * from openrowset (bulk N'http://rates.fxcm.com/RatesXML', SINGLE_BLOB) rs

I get this...