Matching up values within date ranges

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Matching up values within date ranges

Author

Topic

alanlambert
Starting Member

26 Posts

Posted - 2010-07-14 : 10:34:23

I have 2 tables. One that store charge-out rates for employees on project and the date from which the rate became effective. The other table holds timesheet information, recording the numbers of hours that an employee worked on different project on different dates.

What I'm need to get is a list the hours per person per project per date with the appropriate charge-out rate.

A simplified version of the data is:

CREATE TABLE #Rates (
	ProjectCode nvarchar(20) NOT NULL,
	EmployeeCode nvarchar(20) NOT NULL,
	EffectiveDate datetime NOT NULL,
	Rate money NOT NULL
)

CREATE TABLE #Hours (
	ProjectCode nvarchar(20) NOT NULL,
	EmployeeCode nvarchar(20) NOT NULL,
	Date datetime NOT NULL,
	Hours tinyint
)

INSERT INTO #Rates (ProjectCode, EmployeeCode, EffectiveDate, Rate)
SELECT 'Project1', 'Employee1', '2010-01-01', 100 UNION ALL
SELECT 'Project1', 'Employee1', '2010-05-01', 115 UNION ALL
SELECT 'Project1', 'Employee2', '2010-01-01', 80  UNION ALL
SELECT 'Project2', 'Employee2', '2010-01-01', 90  UNION ALL
SELECT 'Project2', 'Employee2', '2010-03-01', 100 UNION ALL
SELECT 'Project2', 'Employee3', '2010-02-01', 50

INSERT INTO #Hours (ProjectCode, EmployeeCode, Date, Hours)
SELECT 'Project1', 'Employee1', '2010-04-20', 5 UNION ALL
SELECT 'Project1', 'Employee1', '2010-04-21', 2 UNION ALL
SELECT 'Project1', 'Employee1', '2010-05-05', 6 UNION ALL
SELECT 'Project1', 'Employee2', '2010-04-25', 5 UNION ALL
SELECT 'Project2', 'Employee2', '2010-01-13', 6 UNION ALL
SELECT 'Project2', 'Employee2', '2010-04-01', 2 UNION ALL
SELECT 'Project2', 'Employee3', '2010-03-01', 3 UNION ALL
SELECT 'Project2', 'Employee3', '2010-04-01', 5

I need to get out:

ProjectCode  EmployeeCode  Date        Hours  Rate
==================================================
Project1     Employee1     2010-04-20  5      100
Project1     Employee1     2010-04-21  2      100
Project1     Employee1     2010-05-05  6      115
Project1     Employee2     2010-04-25  5      80
Project2     Employee2     2010-01-13  6      90
Project2     Employee2     2010-04-01  2      100
Project2     Employee3     2010-03-01  3      50
Project2     Employee3     2010-04-01  5      50

I'm sure I've come across a solution to this before but can't seem to find it again (I think it involved using a calendar table) so any help would be greatly appreciated.

Many thanks

Alan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-14 : 10:41:56

[code]
select *
from #Hours h
cross apply
(
select top 1 Rate
from #Rates r
where r.ProjectCode = h.ProjectCode
and r.EmployeeCode = h.EmployeeCode
and r.EffectiveDate <= h.Date
order by r.EffectiveDate desc
) r
[/code]

KH
[spoiler]Time is always against us[/spoiler]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-07-14 : 11:00:19

or this (if you like more complicated, harder to read solutions :)


;with rts as
(
       select 
 	       ProjectCode
	       ,EmployeeCode
	       ,EffectiveDate
	       ,Rate
              ,rn = row_Number() over (partition by projectCode, EmployeeCode order by EffectiveDate)
       from   #rates
)
, rtRnge as (
       select r1.projectCode
              ,r1.EmployeeCode
              ,r1.EffectiveDate [fromDt]
              ,isNull(r2.EffectiveDate, '3000-12-31') [toDate]
              ,r1.rate
       from   rts r1
       left outer join rts r2
              on r2.projectCode = r1.projectCode
              and r2.EmployeeCode = r1.employeeCode
              and r2.rn = r1.rn + 1
)
select h.ProjectCode  
       ,h.EmployeeCode  
       ,h.Date        
       ,h.Hours  
       ,rr.Rate
from   rtRnge rr
join   #hours h
       on h.projectCode = rr.projectCode
       and h.EmployeeCode = rr.EmployeeCode
       and h.date >= rr.fromDt
       and h.date < rr.toDate
order by h.ProjectCode
       ,h.EmployeeCode
       ,h.date

OUTPUT:
ProjectCode          EmployeeCode         Date                    Hours Rate
-------------------- -------------------- ----------------------- ----- ---------------------
Project1             Employee1            2010-04-20 00:00:00.000 5     100.00
Project1             Employee1            2010-04-21 00:00:00.000 2     100.00
Project1             Employee1            2010-05-05 00:00:00.000 6     115.00
Project1             Employee2            2010-04-25 00:00:00.000 5     80.00
Project2             Employee2            2010-01-13 00:00:00.000 6     90.00
Project2             Employee2            2010-04-01 00:00:00.000 2     100.00
Project2             Employee3            2010-03-01 00:00:00.000 3     50.00
Project2             Employee3            2010-04-01 00:00:00.000 5     50.00

Be One with the Optimizer
TG

alanlambert
Starting Member

26 Posts

Posted - 2010-07-14 : 14:43:04

Thank you both very much. That's extremely helpful.

Alan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources