Mark-101232 (6/27/2012)

---

Are you really using SQL Server 2000? If so, this should work but for SQL Server 2005 upwards there are better solutions.

SELECT s.ID, s.PDate, score, code

FROM #mytable S ...

Nevermind 🙂 I worked it out by adding <= for the effective date, and it works perfectly now. Thanks Lynn!

Hi Lynn,

I poked around with the code, and added a few records. The main one that is most important is the case where I marked it My New Added record....

Thanks Lynn! This looks very close to the results that I need. I'll poke around with it.

Lynn, there are only a few that actually are 'Continuously' active though. They have to be active in December 2011, and January, February, March, April, May, and June of 2012...

Here is a larger code set with some actual data. I need to select only those ID's who have an effective date on or before 12/01/2011, and I need to...

Lynn Pettis (6/21/2012)

---

And before I go any further, please verify what version of SQL Server you are running. I just noticed that this is in a SQL Server 7/2000...

I just want the ID as long as it is continuously eligible from let's say January through June.

Sorry, the post was posted before I was done with it, and it then posted twice. I've made edits to the post that actually describe my challenge. Thanks!

Lynn Pettis (5/16/2012)

---

Sounds like parameter sniffing. I don't have the links handy, so you will need to try searching this site with those keywords. Or, hopefully, someone with...

mldardy (5/11/2012)

---

Hi All,

I tried both Daniel and burninator's code and it seems to be better but not calculating correctly for the totals. This is probably something I'm doing wrong but...

Also found this neat little function, in case you are interested:

create function ProperCase(@Text as varchar(8000))

returns varchar(8000)

as

begin

declare @Reset bit;

declare @Ret varchar(8000);

declare @i...

Sadness.

At least it will come in handy after these tables are...

drew.allen (5/10/2012)

---

This problem is almost certainly due to parameter sniffing. Gail has a good article on Parameter Sniffing[/url]

There are other issues with your code, though. You should use...

Hey everyone, thanks for all of your thoughts and ideas. I stripped the code down to the very basic, hitting only 1 table once, but still keeping my parameter but...