sql - Get the employee wages within an effective date -

i have requirement calculate employee salary within given date range in sql server

my data

employee rate table:

empl_id effective_date  wage     101        5/23/2015    20   101        6/2/2016      30  

project table:

empl_id hrs_dt  project_id hours 101     1/1/2015   10001        8 101     1/2/2015   10001        8 101     1/3/2015   10001        8  ...      ....      .....       .... 101     6/2/2016     ....        ... 

expected result:

empl_id hrs_dt      proj_id hr    wages 101     1/1/2015    10001   8      20 101     1/2/2015    10001   8      20 101     1/3/2015    10001   8      20 ...      ....        ...   ... ....     6/2/2016                   30 

as starting point can join 2 tables on empl_id column , ignore employee_rate rows effective data later project date:

select p.empl_id, p.hrs_dt, p.project_id, p.hours, er.wage project p join employee_rate er on er.empl_id = p.empl_id , er.effective_date <= p.hrs_dt;     empl_id hrs_dt     project_id      hours       wage ---------- ---------- ---------- ---------- ----------        101 2016-01-01      10001          8         20        101 2016-01-02      10001          8         20        101 2016-01-03      10001          8         20        101 2016-06-01      10001          8         20 ...        101 2016-06-02      10001          8         30        101 2016-06-02      10001          8         20 

... can see gives multiple results once you've had wage change. want keep wage value last record, 'last' recent of before project hours date. can use analytic function row_number() decide record is, using subquery , filtering find row 1 - have latest effective date:

select empl_id, hrs_dt, project_id, hours, wage (   select p.empl_id, p.hrs_dt, p.project_id, p.hours, er.wage,     row_number() on (partition p.empl_id, p.hrs_dt       order er.effective_date desc) rn   project p   join employee_rate er on er.empl_id = p.empl_id   , er.effective_date <= p.hrs_dt ) tmp rn = 1;     empl_id hrs_dt     project_id      hours       wage ---------- ---------- ---------- ---------- ----------        101 2016-01-01      10001          8         20        101 2016-01-02      10001          8         20        101 2016-01-03      10001          8         20 ...        101 2016-06-01      10001          8         20        101 2016-06-02      10001          8         30 

tested in oracle original question tag, should work in sql server too.