Date between two Dates


G

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
 
Ad

Advertisements

G

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,
 
Ad

Advertisements

M

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,



Scott said:
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]));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top