It's my understanding that the query optimizer treats them both the same.

Ah Ha!

Thanks.

Jeff,

Actually, after you asked, I did a bit more testing and I was completely backwards in my statement.

So if anyone wants to double check, please do.....

I built a testData table...

Peter, do you have any reasoning why UNION or UNION ALL would be preferred over my solution? Just curious.

Wade,

One thing, you're not handling the case when the EXPIRATION field is equal to 1/1/07, but my code below does. It will put those records into the expired group.

SELECT

...

the CTE

Wow, that's pretty nice PW!

Trigger, can you run both of these against your data and get the execution stats. My test shows PW's is a HUGE amount faster.

AH, you fixed your code right as I made my last post.... LOL

Adam Haines (11/8/2007)

---

Great solution Jason. 😉

The original specification required that the data be in a not incrmental format. For example, if A11 has 2 then it should be...

😀

Now that's funny, but it just goes to show something that I learned a long time ago and struggle to remember every day and that's "Don't assume anything" .... *grin*

No...

Dfalir,

Take a look at Matt's post. He's said it a bit better than I have. But I believe my code does exactly what you stated. Other than your definition doesn't...

I think we're not querying with the same restrictions here. My queries do NOT incorporate an end date as you defined in the first post. What do you get when...

Dfalir (11/8/2007)

---

.... Keep in mind that the years presented are not fixed. They are just 3 years and on from the current year. For example when 2008 come, the...

The only thing I can see is the

TC_NUMBER LIKE ('%' + @Centre + '%')

vs.

TC_NUMBER = '0009'

Are the results still different if you change that part of the test query?

FYI,...

Point well made Bob. 😀