Date between two Dates

[Guest]

I have a table with StartDate, EndDate and Rate and there is a record for
every year. For example, the most current or last record is StartDate 7/1/06
and EndDate 6/30/07 and Rate .063. The previous record is StartDate 7/1/05
and EndDate 6/30/06 and Rate .060. I have another table with CheckDate and
other fields. I want to query the value of the Rate field based on the rate
where the CheckDate falls between the StartDate and EndDate record. So, if
the CheckDate is 1/30/07 then I want to return the rate value of .063 and if
the CheckDate is 1/30/06 then I want to return the Rate value of .060. How
do I do this in a query?

Thanks,

Scott

 

[George Nicholson]

One approach:

The following should return SomeID, CheckDate along with the Rate &
StartDate from the record containing the largest StartDate that is <=
CheckDate (if you don't need to see StartDate, you may want to delete it as
the subquery carries a performance hit if you have a lot of records).

Note that EndDate is unreferenced since it is unnecessary as long as you
ORDER BY StartDate. The presumption is that there is always one, and only
one, Rate in effect. In the example you provided, you have the luxury of
knowing the EndDate but in a lot of similar situations you might not. You
might have historical & current commision, tax or interest rates, but have
no idea when the EndDate of the current rate will be, therefore this
solution does not require that knowledge.


SELECT MyData.SomeID, MyData.CheckDate, (SELECT TOP 1 [Rate] FROM MyRates
WHERE [StartDate] <= [CheckDate] ORDER BY StartDate DESC) AS Rate, (SELECT
TOP 1 [StartDate] FROM MyRates WHERE [StartDate] <= [CheckDate] ORDER BY
StartDate DESC) AS StartDate
FROM MyData ORDER BY MyData.SomeID;

HTH,

 

[Michael Gramelspacher]

One approach:

The following should return SomeID, CheckDate along with the Rate &
StartDate from the record containing the largest StartDate that is <=
CheckDate (if you don't need to see StartDate, you may want to delete it as
the subquery carries a performance hit if you have a lot of records).

Note that EndDate is unreferenced since it is unnecessary as long as you
ORDER BY StartDate. The presumption is that there is always one, and only
one, Rate in effect. In the example you provided, you have the luxury of
knowing the EndDate but in a lot of similar situations you might not. You
might have historical & current commision, tax or interest rates, but have
no idea when the EndDate of the current rate will be, therefore this
solution does not require that knowledge.


SELECT MyData.SomeID, MyData.CheckDate, (SELECT TOP 1 [Rate] FROM MyRates
WHERE [StartDate] <= [CheckDate] ORDER BY StartDate DESC) AS Rate, (SELECT
TOP 1 [StartDate] FROM MyRates WHERE [StartDate] <= [CheckDate] ORDER BY
StartDate DESC) AS StartDate
FROM MyData ORDER BY MyData.SomeID;

HTH,

I have a table with StartDate, EndDate and Rate and there is a record for
every year. For example, the most current or last record is StartDate
7/1/06
and EndDate 6/30/07 and Rate .063. The previous record is StartDate
7/1/05
and EndDate 6/30/06 and Rate .060. I have another table with CheckDate
and
other fields. I want to query the value of the Rate field based on the
rate
where the CheckDate falls between the StartDate and EndDate record. So,
if
the CheckDate is 1/30/07 then I want to return the rate value of .063 and
if
the CheckDate is 1/30/06 then I want to return the Rate value of .060.
How
do I do this in a query?

Thanks,

Scott

Also perhaps:

SELECT MyData.CustomerID,
MyData.CheckDate,
MyData.CheckAmount,
MAX(MyRates.Rate) AS CurrentRate
FROM MyData,
MyRates
GROUP BY MyData.CustomerID,MyData.CheckDate,MyRates.StartDate,
MyData.CheckAmount
HAVING (((MyRates.StartDate) <= [MyData].[CheckDate]));