[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1008
  • Last Modified:

SQL to join two tables with Effective date ranges

I am trying to understand the best/right way to link two tables together with SQL code when the key that links the two is a date range. I will provide a very simplified table structure and if I can understand this then I should be able to apply it to my application. Consider an employee table and Rate table as follows
EMPLOYEE TABLE                                                        RATE TABLE
EmpNum    StartDate                                                     EffDate        Rate
00001        02/02/07                                                     01/01/07       1.00
00002        04/04/07                                                     03/03/07       1.10
00003        06/06/07                                                     05/05/07       1.20

In the above example I need to link the Employee table with the Rate table. The match occurs when EMPLOYEE.StartDate is >= RATE.Effective but < the next RATE.Effective. Essentially the way it is laid aout above would be correct. How would I write SQL Code that would join the two tables as required?
0
Rob4077
Asked:
Rob4077
  • 5
  • 3
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
you can use Dlookup function to get the rate for the employee based on the Startdate.
if you want it in a query, you can use a function to get the rate.
0
 
Rob4077Author Commented:
Is that the usual approach, even when you have a lot of records to process in a loop? EG if I need to process several hundreds of records and use the DLookup function for each one it will make processing very slow.On the other hand I thought of entering another field in the table, called Until, and loading it with the effective date of the next record. If I did that I could use code something like the following and it would create exactly the recordset I need that would allow for quick processing.    
      SELECT tblEmployee.Name, tblEmployee.StartDate, tblRates.Effective, tblRates.Rate
      FROM tblEmployee INNER JOIN  tblRates ON tblEmployee.StartDate >= tblRates.Effective AND
      tblEmployee.StartDate < tblRates.Until;
Problem is I then carry the overhead of maintaining an accurate Until date and it could be called redundant data. Is the professional approach to use the approach you recommended?
0
 
JR2003Commented:
This should work

SELECT E.Name,
       E.StartDate,
       R.Effective,
       R.Rate
  FROM tblEmployee E
 INNER JOIN tblRates R
         ON R.Effective = (SELECT Max(R1.Effective)
                             FROM tblRates R1
                            WHERE R1.Effective > = E.StartDate)

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
JR2003Commented:
That should be '<=' not '>='

SELECT E.Name,
       E.StartDate,
       R.Effective,
       R.Rate
  FROM tblEmployee E
 INNER JOIN tblRates R
         ON R.Effective = (SELECT Max(R1.Effective)
                             FROM tblRates R1
                            WHERE R1.Effective <= E.StartDate)

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
in the manner your table was set up, the only way i could think of is to use a function to get the rate. Place this codes in a module

Function GetRate(d As Date) As Double
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select * from tblRates order by effective desc")
rs.MoveFirst

Do Until rs.EOF
    If rs("effective") <= d Then
        GetRate = rs("rate")
        Exit Function
    End If
    rs.MoveNext
Loop
rs.Close
End Function

to use in a query

select *, getrate([startdate]) from tblemployee
0
 
Rob4077Author Commented:
JR2003, I tried your code and get the following error. I basically understand what the logic is trying to do but not the aliases. I tried to understand how the alias definitions work but I think I need to do some homework to figure out the solution myself. You may know quickly how to handle it. The error is "syntax error in query expression 'R.Effective = (SELECT Max(R1.Effective)
                                      FROM tblRates R1
                                     WHERE R1.Effective <= E.StartDate);'

Capricorn1, Your approach worked and I can understand it. Thanks.

I now have two approaches to the same problem and with a little effort I have little doubt that JR2003's solution will work. So is there an inherent advantage to either approach or are they both essentially equally efficient?
0
 
Rob4077Author Commented:
Sorry if my question made you feel awkward. I am not trying to get you to tell me who is the better Expert, I am merely trying to learn from my last question and wondered what the difference between the two approaches is internally, as far as MS Access processing is concerned.

JR2003 at this stage I still can't figure out what I need to do to get your code to work. Can you please help?

Rob
0
 
JR2003Commented:
Sorry my query was for SQL-Server so I'm not sure that it will work in access.
Sometimes in access you have to break down a query into separate queries and join the queries together.
0
 
Rob4077Author Commented:
Oh, that explains why your code won't work. I'm afraid I don't know how to make it work in MS Access so unless someone can help it leaves capricorn1's solution as the only viable one. I will leave the question open for a while to see if I get any more comments.
Thanks again for trying to help.
0
 
Rob4077Author Commented:
Looks like yours is the right and best solution for this. Thanks Capricorn 1. I appreciate your help in solving the puzzle and helping me to get a better grasp of how MS Access works.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now