This looks a bit better on plan and will also let you return the dates:

select gpa.*, gpb.*

, gpa.Running_Sum - gpb.Running_Sum as diff

from dbo.grouped_Performance gpa

join dbo.grouped_Performance gpb

on gpa.Row_Order < gpb.Row_Order

and gpa.Running_Sum...

Hi,

The following should help, I think. That said, the final query to return the result looks quite inefficient (triangular join?) - there's probably a smarter way. I'll keep playing around...

Hi keymoo,

Am I right in assuming that the drawdown has to be continuous series of down movements? E.g if the value went:

10,9,8,7,8,7,6,5,4,5

Then I have two drawdowns, one from 10...

How about:

select * into bbq

from table2

/*

result

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

nom nom nom nom nom

(1 row(s) affected)

*/

Or, seriously, try looking at charindex and patindex in BOL - they should help you <to finish...

Hi,

Why not just append the data to global temp table?:

create table ##myResults (result1 varchar... etc...)

while...

set @sql = 'insert ##myResults select Value1... etc

exec @sql

<next db>

end

select * from...

Hi, this should get you somewhere near:

;with cte as (

select ca.attributeId

, ca.value as ca_value

, sa.supplierId

, sa.value as sa_value

from ClientAttribute ca

join SupplierAttribute sa

on ca.attributeId = sa.attributeId

where ca.clientId = 1

)

, cte2 as...

I'll second that Wayne, it's nice to see.

Bumped up the content in the temp table a bit:

select count(*) from SomeTableX = 2080

edit: forgot params

SET @periodslength = 6

SET @periodsnumber = 9

Test

dbcc dropcleanbuffers

dbcc freeproccache

set statistics io...

Hmm, a real mess indeed. Not sure why, possibly some disconnect between criteria for each select?

Bezan (9/2/2010)

---

- another...

No worries, always more than one way to skin a Cat.

I think one of the problems is the convert function:

CONVERT('2009-10-28', CURRENT_TIMESTAMP, 23)

Usage is

CONVERT ( data_type [ ( length )...

D'uh, you're on 2K5 of course. Can't assign variable values in the declare statement.

Try this (note not now split into two chunks, so table rewrites every time:

-- clear temp table

if...

What errors do you get? Works fine for me?

This should give you the result you expect. Again, I've split it into two chunks for testing, you'll probably want to combine the two sections into a single procedure.

Run...

I was going to procrastinate on it, but I think I'll do that tomorrow....

[drum roll please]

😀

Hi,

This function might help - but because of the crunching it's likely to be quite inefficient so it depends how much data you have to process and how often...