I am not sure if this is possible or not, I consider myself pretty decent with sql and I am stumped on this any help would be greatly appreciated.

Here is the scenario I have a table with a bunch of column and my last two columns are EFFI(Effect In Date) EFFO(Effect Out Date) our users recently loaded a bunch of data with an EFFI date of 1-15-2005 and an EFFO 12-31-9999, next year this time the will copy the same data but may change a column here or there but will make the EFFI date 1-15-2006 and EFFO 12-31-9999, we don't want to have to have our users effect out the previous years dates we want our queries to pick up the most recent price list record.

When the application runs a user will enter a price date and we will give them a price based on the date the entered so for example if the entered a price date of 2/15/2005 we will pickup the record 1-15-2005 to 12-31-9999 but next year is going to be an issue because if I entere a price date of 2-15-2006 I will pickup both price records because I am using a BETWEEN in my where clause:ex.
WHERE '2006-02-15' BETWEEN EFFI AND EFFO
Is anyone anywhere of a way to pick up the most current record the 1-15-2006 record, you can't use MAX in a where clause, I have tried numerous things such as a setting EFFI = subselect where this grabs the max date but this grabs the max date of all records in table instead of max date based on criteria.

Again any help would be greatly appreciated, I am stumped.

I undetstood your problem, but I may help if you send your sql and see if you are joining with any other table which has dt_eop.

Then may be you should use those criteria in the subselect?

Alternatively,

Thanks for the replies:

The suggestion of WHERE '2006-02-15' BETWEEN EFFI AND EFFO
AND YEAR('2006-02-15'') = YEAR(EFFI) would work perfect except if someone changes our new effective price in the same year. Let me give you a couple of examples and my sql

Database record
ID Price EffI Effo
1 234.00 2005-01-01 9999-12-31
2 250.00 2005-03-01 9999-12-31
3 275.00 2005-06-01 9999-12-31

I understand some of the effect in dates are in the future but our application allows you to type in date and base on that date it will return you the price.

SELECT PRICE from TABLE WHERE ? BETWEEN EFFI AND EFFO;

So if the parameter passed in was 1/15/2005 I want 234 returned
if paramater 3/15/2005 is passed in I want 250 returned and anything passed
in with a date after 6/1/2005 should return 275. I hope this explains my dilemma a little more, we don't want to have our users change EFFO on previous records prior to the next EFFI.

Thanks for the suggestions already!

select price from table where ? between effi and effo order by effi desc fetch first 1 row only
This I guess will do the trick ... But, the appropriate way will be to change the effi each time a new record is inserted or an exisiting row deleted or updated ... You may do this using a trigger ...

sorry , i meant effo

I think your data model is inefficient in that case. The first price in the above example isn't effective from 2005-01-01 to infinity; it is only in effect until 2005-03-01. So, if you end-date the first record to have EFFO = '2005-03-01' then your query would work (well, with little modification: "...BETWEEN EFFI AND (EFFO - 1 DAY)...").

If for some reason you decide to keep you current model then your query should be different. First of all, since your upper limit is equivalent to "inifinity" whatever date you enter as a parameter it will always be less than '9999-12-31', that is the comparison with EFFO is redundant. You only need to compare the parameter with EFFI, like this:

To expand a little on n_i's response: Greg Hannan (for those of you who know him) once suggested that

1) store effo as the date on which the next price become effective. This makes it easy/efficient to check that there isn't a hole in the date range
select ... from ... a where not exists (select 1 from .... b where a.effi=b.effo)
- no date arithmetic means indexability

So when you insert a new price/effi, use a trigger to change the effo of the previous "current price" row.

2) use effo in descending sequence in the index. Think about how
where effi <= ? and effo > ?
will be processed. You'll might have many effi dates in the past, but only a few effo dates in the future.

James Campbell