Adam, I think part of the problem is, the formula doesn't work as everyone expects  because DATEDIFF only looks at boundaries crossed of the date part...

As someone else suggested, why make the AGE persist?  I'm thinking it would be much easier to add a calculated column to the table so that you don't have to...

Well, I retuested with the correction Giovanni noticed (see last posting) and was a bit surprised... the change did nothing to the low end (1st page still returns in 13 milliseconds)...

Giovanni... thanks for the questions.

The * selection in the top select of "d" was a nasty oversite  ... it was a leftover from testing...

Subhash,

I'm sorry I left out the the "--" on the comment  .  It sure did make a mess of things.  You all set now?

... or try Books OnLine which comes with SQL Server.

That would do it, but be careful... setting the transaction isolation level in procs may sometimes cause a conflict for lock type.  You could use the WITH (NOLOCK) hint after the...

Just so you know, you can use VARCHAR(8000) for this... won't help with things over 8000 characters like Peter's suggestion but you don't have to limit yourself to 4000 characters...

Better late than never, I guess...

DECLARE @Year    CHAR(4)

DECLARE @NextYear CHAR(4)

DECLARE @ISOWeek VARCHAR(2)

    SET @Year    = '2001'

    SET @ISOWeek = '53'

    SET @NextYear = @Year+1

SELECT

CASE

WHEN...

Oh CRUD!  No, you don't even want to think of building a sequence table for this...  here's why...

You said that the 3rd party stuff

Kenneth is absolutely correct... if you're not going to use IDENTITY, then you really must have a counter/sequence table and a stored proc to get the nextid.  Repost if you...

Senthil,

I'll probably get a real blast of hooey  for using Dynamic SQL but don't much give a rat's patooti  because it runs in...

Subhash,

For the first problem, use the UNION query you made as if it were a table and do the GROUP BY on that... like this...

 SELECT d.EmpNo, SUM(d.Sal) AS...

declare @dbname varchar(100)

    set @dbname = 'Some Database'

DECLARE @sql VARCHAR(8000)

    SET @sql = 'use ' + @dbname

             + ' dbcc shrinkfile (''File'', Size)'

   EXEC (@SQL)

...should work for what...

I don't beleive that moving the SELECT into a transaction will prevent others from writing to the target table and, therefore, will not prevent you from inserting a duplicate ID...