I used while loop to do this. But it is taking more than 30 minutes to complete the execution.

I used the below logic:

select assetid,dt,count(dt) as dt

into #multiples

from #t1

group by assetid,dt

loop...

Any inputs?

I am not still clear with logical IO part. could you explain it little bit more?

I have tried the below query. It gives the expected result. is there any other way to do the same?

select b_id

,ac_id

...

Gail,

Wow! It is working perfectly:)

Thanks a lot

create table balance_due

(

name varchar(15),

balance_due_as_on datetime,

Amount float

)

go

insert into balance_due

select 'Ravi','11/09/2010',112.50

union all

select 'John','11/09/2010',100.25

union all

select 'Ravi','11/08/2010',112.50

union all

select 'John','11/08/2010',100.25

union all

select 'Ravi','11/05/2010',112.50

union all

select 'John','11/05/2010',200.75

union all

select 'Ravi','11/04/2010',111.00

union all

select 'Ravi','11/03/2010',105.25

union all

select 'John','11/03/2010',105.00

union all

select 'John','11/02/2010',100.25

union...

I have also seen the below code

WHERE ABS(Round(@AVG - @Return,2)) >= @TSTTData

I hope this one also lead performance issue.

Let me take a deep look into the query.

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE FUNCTION [dbo].[fnGetValueAndType]

(

@Value int

,@Typevarchar(100)

)

RETURNS VARCHAR(1000)

AS

BEGIN

DECLARE @Name VARCHAR(1000)

SELECT @Name = [Name]

FROM tCodeLookup

WHERE Type = @Type

AND [Value] = @Value

AND IsActive = 'Y'...

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE FUNCTION [dbo].[fnGetAsset]

(

@AssetIDINT

,@AssetType_CodeINT

)

RETURNS INT

AS

BEGIN

DECLARE @DataSourceIDINT

DECLARE @AssetTypeNameVARCHAR(100)

SET @AssetTypeName = dbo.fnGetValueAndType(@AssetType_Code, 'AssetType')

IF @AssetID IS NULL OR @AssetType_Code IS NULL

SET @DataSourceID = NULL

ELSE

BEGIN

IF UPPER(@AssetTypeName) = 'HFUND'

SELECT @DataSourceID...

CREATE TABLE #Results(

,Issue VARCHAR(500)

,AssetID INT

,AssetName VARCHAR(1000)

,AssetType_Code INT NULL

,AssetTypeName VARCHAR(1000)

,DataSourceID INT NULL

)

how to achieve this by using CLR? I don't have any idea about CLR.

any inputs?

It ran last friday only in DEV.

is there any other approach to get the AVG() values?

how should i reduce the execution time in seconds? give me some tips...

I have created a nonclustered index on mf_cd column..I see some improvement. yes...the execution time is reduced from 30 minutes to 15 minutes...