asked on

Select a rate from a rates table

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.

cyberkiwi

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

Ausway

ASKER

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.

nmarun

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

Ausway

ASKER

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 Gs

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 Gs

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

Ausway

ASKER

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

Rajkumar Gs

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

Raj

Ausway

ASKER

Great, thank you, I will do some testing now!

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.