Solved

SQL to join two tables with Effective date ranges

Posted on 2007-11-15
10
553 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 119

Expert Comment

by:Rey Obrero
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
 
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 119

Accepted Solution

by:
Rey Obrero 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now