Hardly. I just get a lot of mileage out of stolen techniques and some lucky guesses. 😛

Truly, you are the man, Flo. 😉

HAH! I was right!! (cruel chuckle)

The DENSE_RANK solution adds a sort and segment to the execution plan, so the DATEDIFF solution runs twice as fast...

Isn't DENSE_RANK going to force a sort?

declare @startDate datetime

set @startDate = '11/1/2009'

select DATEDIFF(month,0,@startdate)-1317 -- this datediff calculation is the solution

-- proof

;with tally (N) as (select row_number() over(order by id)...

PARTITION BY means to start counting all over again from 1 when this value changes. It isn't required.

Try this:

,row_number() over (ORDER BY day_date)

But, if you have one...

First off, you shouldn't be getting ANY rows back based on that criteria. Yet another reason we ask to see actual code.

You can rewrite it...

Here is an example that just logs the first names instead of emailing them, but you should get the idea.

You didn't mention whether or not you might...

Presenting or attending, Gail?

Whups

Good luck with your 2 a.m. presentation, Gail.

Kruti:

CONVERT is unecessary if the target is large enough, and won't help if it isn't.

-- this will fail

declare @source int

declare @target numeric (6,2)

set @source = 35677

set @target = CONVERT(numeric(10,2),@source)

select 'Example...

When you get to 2008, you will also be able to pass table variables as input parameters. However, we have a lot of code that parses strings...

Sure hope you're feeling better, Barry. If not now, then soon.

select * from #test b

where b.id= (select max(id) from #test t

...

For starters, I'd like to know which steps are taking the most time. If you are doing a table scan of a zillion large rows, that could...