Select a rate from a rates table

Ausway
Ausway used Ask the Experts™
on
Hi Experts,
 
I am trying to select a rate from a rates table for a particular employee using a combination of Employee and ValidFrom to narrow down the rate.
 
Employee    ValidFrom               Rate
ABC               11-08-2009              100
ABC               22-05-2010              120
 
The report passes a ToDate and FromDate parameter to the stored proc that I can reuse for the ValidFrom hence
 
when I run the report for period 01-05-2010 to 07-05-2010 I do not get any rate as there is no
ValidFrom between 01-05-2010 to 07-05-2010.
 
For the period between 01-05-2010 to 07-05-2010 I need
ABC               11-08-2009              100
 
For the period between 01-06-2010 to 07-06-2010 I need
ABC               22-05-2010              120
 
What would be the best approach to tackle this?
 
Many thanks in anticipation.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
create proc getEmployeeRate
@employee varchar(10),
@fromDate datetime,
@toDate datetime
as
select Employee, ValidFrom, Rate
from rates
where Employee = @employee and ValidFrom between @fromDate and @toDate

Author

Commented:
Thanks Sage.

I already have a stored proc that looks something like this;

select
t.employee
,(select rate from rates  where employee = t.employee and validfrom between @FromDate and @ToDate) as rate
from transactions t
where transdate between @FromDate and @ToDate

Does not quite work.

Top Expert 2009

Commented:
Well firstly, his ranking is Sage.. it sounds funny (but definitely not offensive) when you start calling him like that. I've seen people say 'Thanks cyberkiwi'.. but this is the first.

Secondly, your query is a more complicated than it needs to be. Use the query given by cyberkiwi.

As a third point, what is the datatype of the ValidFrom column? If it is not of datetime, then the above query will give unexpected results.

Arun
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Thanks for the pull up nmarun, not very technical feedback but a true point.

The validfrom is a datetime datatype. In addition, executing the sp as above will only give me the rates, i have a stored procedure that extracts all transactional data for projects/employees etc and I need a specific rate for that particular project/employee. Running above wont do that???
Rajkumar GsSoftware Engineer

Commented:
Try this query. Replace the table name with yours. Also take care of the date format

Regards
Raj
-- For the period between 01-05-2010 to 07-05-2010
SELECT * FROM #table R
	WHERE Employee = 'ABC' AND ValidFrom = 
		(SELECT MAX(ValidFrom) FROM #table 
			WHERE Employee = R.Employee AND ValidFrom <= '07-05-2010')

-- For the period between 01-06-2010 to 07-06-2010
SELECT * FROM #table R
	WHERE Employee = 'ABC' AND ValidFrom = 
		(SELECT MAX(ValidFrom) FROM #table 
			WHERE Employee = R.Employee AND ValidFrom <= '07-06-2010')

Open in new window

Rajkumar GsSoftware Engineer

Commented:
The query I posted above will fetch the latest rate applicable with regard to the ValidTo date.
What result you expect if there is more than one rate is applicable for that date range ?

Raj

Author

Commented:
Thanks RajkumarGS. If there is more than one rate, then it should take the latest rate. ie max(Recid).
Rajkumar GsSoftware Engineer

Commented:
Well. When I tested with sample data, my query is fetching like that.

Raj

Author

Commented:
Great, thank you, I will do some testing now!
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Just a quick change to your query will do

select
t.employee
,(select top 1 rate from rates
  where employee = t.employee and validfrom between @FromDate and @ToDate
  order by validfrom desc) as rate
from transactions t
where transdate between @FromDate and @ToDate

>> then it should take the latest rate. ie max(Recid).
Not sure what system you are running, but in many systems I work with, max(Recid) does not guarantee "latest".  So I went by validfrom instead.

Author

Commented:
Thanks for your assistance

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial