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!