Select closest date (previous to) for a given date

[tmunson99]

I have an employee table with changes in positions, Org, labor categories, pay, etc. (This is a simplistic view of what I'm doing, but I get the idea across.)

[Employee]

EmplId EffectDate OrgId Position PayRate
1234 1/1/2006 1.1.1 Laborer 10.00
1234 4/1/2006 1.2.4 Tech 12.00
1234 6/1/2006 1.5.2 Manager 15.00

I have a second table with timesheet entries

[Timesheet]

EmplId WorkDate Hours
1234 1/1/2006 8
1234 1/2/2006 8
1234 1/3/2006 8
....
1234 5/1/2006 8
....
1234 6/2/2006 8

I need to join the activity from the Timesheet table with the Employee data for a report but I want to show the Employee's OrgId, Position, PayRate he would have been at on that timesheet date. In other words, all timesheet activity after 4/1/2006 and prior to 6/1/2006 would be 'charged' to the position he came into on 4/1/2006 and all activity after 6/1/2006 would be 'charged' to the position he came into 6/1/2006.

Along the lines of what I've tried but DOESN'T work is:

SELECT EmplId, OrgId, Position, PayRate, WorkDate, Hours FROM Employee
JOIN Timesheet ON Employee.EmplId = Timesheet.EmplId
WHERE WorkDate >= EffectDate AND WorkDate < EffectDate

Any help would be appreciated!

 

[gmmastros]

This query is a bit tricky because you are not storing the 'end date' for an employee's position (and I'm not recommending it either). If the end date had been in the employee table, then this query would have been relatively simple. So... the first challenge will be to manufacture an end date. The following query will 'manufacture' an end date.

Select EmplId, EffectDate As StartDate, OrgId, Position, PayRate,
       IsNull((Select Min(EffectDate) As EndDate 
        From   Employee B 
        Where  A.EmplId = B.EmplId 
               And A.EffectDate < B.EffectDate 
       ), GetDate() + 1) - 1  As EndDate
From   Employee A

Now, we will use this information to join to the timesheet table, like this...

Select T.EmplId,
       Emp.OrgId,
       Emp.Position,
       Emp.StartDate,
       Emp.EndDate,
       T.WorkDate,
       Emp.PayRate,
       T.Hours,
       Emp.PayRate * T.Hours As Wages
From   (
       Select EmplId, EffectDate As StartDate, OrgId, Position, PayRate,
              IsNull((Select Min(EffectDate) As EndDate 
              From   Employee B 
              Where  A.EmplId = B.EmplId 
                      And A.EffectDate < B.EffectDate 
              ), GetDate() + 1) - 1  As EndDate
       From   Employee A
       ) As Emp
       Inner Join TimeSheet T
         On Emp.EmplId = T.EmplId
         And T.WorkDate Between Emp.StartDate And Emp.EndDate
Order By T.EmplId, WorkDate

Hop this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tmunson99]

Thanks! I appreciate it. That does make sense. I will try it.