Have you considered the option to simply ignore the dates that were older than X months app side?  Since you were already doing some recoding either way would have been...

Something like this should do it :

SELECT RIGHT(ActivityNo + SPACE(9), 10) + '|' + Designation FROM

(SELECT CONVERT(VARCHAR(10), ActivityNo), RTRIM(LTRIM(Designation)) FROM tableX GROUP BY ActivityNo, RTRIM(LTRIM(Designation))) dtA

The scans go FROM 2 to 10 which is expected but the IO barely go up.  Very nice to see that .

I see your point...  but I think there's a limit to bulletproofing the code .

use master

go

select top 100000 rand(checksum(newid()))

...

The only editing...

On sql server 2000 (with a few user objects ± 10)) :

SELECT COUNT(*) FROM master.dbo.SysComments

2268

So 100K with 3 cross joins is really not a problem .

Assuming you can't live without the index, you'll have to wait while it creates itself.  Maybe you can wait to create it off hours when the server is less busy. ...

Does this do the trick :

SELECT ID, Name, XType FROM dbo.SysObjects WHERE ID = @SomeParam ORDER BY Xtype, Name

Ya and if you have 522 rows with that information, you might consider making it a lookup table!!!

Points still to be talked about.

Why allow all those nulls.

Why iatr the quantity and price fields not declared as decimal.

Where is the unique constraint.

Or by telling it just like this on the boards.  Thanx ez, that'll help someone else someday.

Is the job still opened?

Nice to know. Thanx.

There's not right answer here... they but work .

I'm simply redirecting everyone to a single thread.  That way there will be only 1 active discussion.

Also users finding this thread using a search engine will have access to the...

SELECT

    a.PolicyNumber,

    a.TableCode,

    a.SeriesCode,   

    a.LossDate,

    a.EffectiveDate,

    SUM(b.WaiverofPremium) AS SumPremium

FROM tableA a

    INNER JOIN tableB b

    ON a.PolicyNumber = b.PolicyNumber AND a.TableCode = b.TableCode AND a.SeriesCode = b.SeriesCode

WHERE DATEADD(yyyy, 10,...