After including that line, it is running fine now.

Jeff,

I really learned some good things from your article. Thanks for my sql teacher.

you should continue to try to resolve this...

Mike,

Thanks for your reply.

Actually i did a small mistake in the code. Thats why it gave wrong output. Yes, I missed to include the below line.

Declare @NAV float

Declare @prev_assetid ...

Declare @NAV float

Declare @prev_assetid int

declare @prev_date datetime

SET ANSI_NULLS ON

update Returns_t

set @NAV = NAV = case when (Asset_ID = @prev_assetid) then case when datediff(month,@prev_date,Date) <> 1

...

Any inputs?

Requirement #1:

delete from Returns_t

where Date = '31/oct/2009'

and asset_id = 12

if i delete the above row then the expected output is

122009-04-30 00:00:00.0001.01222009-04-01 00:00:00.000100201.22

122009-05-31 00:00:00.0001.02132009-04-01 00:00:00.000100202.13

122009-06-30 00:00:00.0001.0312009-04-01 00:00:00.000100203.1

122009-07-31 00:00:00.0001.11032009-04-01 00:00:00.000100211.03

122009-08-31 00:00:00.0001.11132009-04-01...

since the data's are confidential , i posted some dummy data's. Actually this table has more than 200,000 records. So i just moved all the records into a temp table...

CREATE TABLE Returns_t

(Asset_ID INT

,[Date] DATETIME NULL

,[Return] FLOAT NULL

,InceptionDate DATETIME NULL

,InitialNAV FLOAT NULL

,NAV FLOAT NULL

)

insert into Returns_t

select 112 ,'30/apr/2009', 0.0122,'30/apr/2008',100,null

union all

select 112, '31/may/2009', 0.0213,'30/apr/2008',100,null

union all

select 112 ,'30/jun/2009', 0.0310,'30/apr/2008',100,null

union all

select 112,...

Any inputs?

I saw huge performance improvement after implementing 'quirky method'. Yes, The execution time was reduced from 45 minutes to 30 seconds( Thanks a lot to Jeff)

But i need to implement...

Yes...i read the article...

when this decision was taken ? and from which version this decision is being followed?

I am just wondering becuase select 1+2+3+null gives 'NULL'. So the optimizer has some inteligence here.

But the same optimizer act like a fool when select sum(columnname) executed.

so the...

Any SQL events are running now or going to start near to NewJersey or NewYork?

Any inputs?

Database designing/modeling

Greg,

They will pass it as STRING.