Daterange for a daterange

It's late and I'm having a hard time figuring out how the heck to build my where clause.

Here's a sample table with data:

ID int
Value int
StartDate DateTime
EndDate DateTime

1 | 100 | 1/1/2004 | 1/23/2004
2 | 200 | 1/23/2004 | NULL

For all intents and purposes, the second record has a null end date because it's valid until a new value is entered. If I were to update the value again, the 3rd record would look like this.

3 | 300 | 1/24/2004 | NULL

And, since this was updated, I'd go back and update the 2nd record so that I know the End Date (the 3rd record's start date)

2 | 200 | 1/23/2004 | 1/24/2004

Ok, with that said, my application looks at each week in a year, and looks for a valid value for the given date. I need to say "for this week, give me the value." If 2 values fall within the given week, I want to grab the highest (MAX) value.

Any ideas on how I'd structure the SQL statement for this? The where clause is where I'm having funny (hey, that's kinda funny -- where and where).

Anyway, I appreciate any help that you all can give me on this one. It's getting late and my brain is burnt out for the day!Anybody? Sorry to bump this, I just though I may have had a bite or two by now.|||checking your clause...
hope is on the way !|||"for this week, give me the value."
If 2 values fall within the given week,
I want to grab the highest (MAX) value.

what is the value that you're talkign about ?
startdate ?|||ID int
Value int <-- [ this is what I'm getting ]
StartDate DateTime [ used in where clause ]
EndDate DateTime [ used in where clause ]|||and when you say "this week"
what kind of parameter are you sending

date ?
no of week in year ?|||A date range.

i.e. 2/8/2004 - 2/14/2004|||is this what your looking for ?

select max(value)
from range
where '2/8/2004' between startdate and enddate or
'2/14/2004' between startdate and enddate|||Hmm, that may work, BUT, there is still the situation where the most recent value does not have an enddate. I guess I'm going to need to OR that in. Any ideas on that?|||maybe better ?

select *
from range
where '2/8/2004' between startdate and isnull(enddate,'1/1/2999') or
'2/14/2004' between startdate and isnull(enddate,'1/1/2999')|||magical transformation of null to a far-far-far-date|||I think that will work out just fine! :)

Thanks a bunch, I'll work in implementation and check back if I forgot something. You have helped me past my brain fart, thanks!|||on sql i'm good enough to help a bit