?
Solved

SQL to join two tables with Effective date ranges

Posted on 2007-11-15
10
Medium Priority
?
826 Views
Last Modified: 2011-08-18
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
Comment
Question by:Rob4077
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20288558
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
 

Author Comment

by:Rob4077
ID: 20288647
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
 
LVL 18

Expert Comment

by:JR2003
ID: 20288801
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.

 
LVL 18

Expert Comment

by:JR2003
ID: 20288807
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 20288823
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
 

Author Comment

by:Rob4077
ID: 20288956
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
 

Author Comment

by:Rob4077
ID: 20297376
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
 
LVL 18

Expert Comment

by:JR2003
ID: 20297584
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
 

Author Comment

by:Rob4077
ID: 20298008
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
 

Author Comment

by:Rob4077
ID: 20326094
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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question