Solved

SQL to join two tables with Effective date ranges

Posted on 2007-11-15
10
580 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 500 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

773 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