Although the new functionality may help with running total, it does not help when trying to roll through a table being able to update a row based on values from...

Jeff, hopefully you see this... I have decided to implement the Quirky update in a specific part of our app, but in order to do so I have to solve...

Joe Celko (7/22/2010)

---

Standard SQL has the syntax and you can get it in DB2 and Oracle, but SQL Server is still behind. The Window clause has a [RANGE | ROWS]...

The way they are written currently I use "LOCAL FAST_FORWARD READ_ONLY" and they are relatively quick though Quirky Update is about twice as fast. The problem I have and...

My problem with using the Quirky Update is this: The procedures in our app that would be rewritten to use the Quirky Update in place of Cursors are very pervasive...

Hey Jeff,

I have read through the discussion pages related to your article titled "Solving the Running Total & Ordianl Rank Problems". There is also another thread with plenty of...

Here is an example of a solution using an outer cursor with an inner recursive CTE. I'm trying to eliminate the outer cursor (and return it all as a...

So I'm running into the following:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion

And looks like the max is 32,000, did you run this on the...

Ok, so I got it. My question now is how does this perform relative to doing the same thing with fast forward read only cursors? I can answer...

Thanks for the input guys. The rows need to be calculated in dayid desc order. Here are the expected results.

AssetID_inSymbolDayID_inMV ...

Running SQL2008

Also Ten Centuries, your solution violates the rules of using the "Quirky Update" by using a Join. I'll add a link to the article on "Quirky Update".

http://www.sqlservercentral.com/articles/T-SQL/68467/[\url]

I don't...

So I think I have a solution that involves dropping the CI and creating a new one in DayID ASC order then just copying values from row 1 to 2,...

So here is code to create the results that I'm referring to:

create table #DMVRunningPerformance (

LongPosition_bt bit null,

AssetID_in int null,

Symbol_vc varchar(255),

DayID_in int,

Status_vc varchar(255) null,

MV float,

DailyPerf float null,

RunningPerf float null)

CREATE CLUSTERED INDEX...

So there is one more problem I am having with the update, my performance numbers are a day off. So here is an example of the results:

HHDayID_inCFQtyCosstMVDailyPerfRunningPerf

443275694013313.6960201048.898700

443275693013313.696143.86201048.898700

443275692013299.8420200841.56580.001032320.00103232

443275691013299.84224.98200321.70430.0025951330.003630133

443275690013299.2495.4201120.0467-0.003969482-0.000353759

443275689013299.1220201779.8805-0.003270068-0.00362267

443275688013299.1220201651.39170.000637183-0.002987795

443275687013299.1220201651.39170-0.002987795

Sorry for the...

Here's the actual udpate statement:

update #DMVRunningPerformance

set

@NewGroup_bt=CASE WHEN @AssetID_in<>AssetID_in or @LongPosition_bt<>LongPosition_bt THEN 1 ELSE 0 END,

@DailyPerformanceNet_dc =CASE WHEN PerformanceMarketValue_fl = 0 or @NewGroup_bt=1 THEN 0

ELSE (@NDPerformanceMarketValue_fl-@NDCashFlowNet_fl-PerformanceMarketValue_fl)

/PerformanceMarketValue_fl

END,

@DailyPerformanceGross_dc = CASE WHEN PerformanceMarketValue_fl...