Solved

SQL to join two tables with Effective date ranges

Posted on 2007-11-15
10
517 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Rob4077
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

762 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

16 Experts available now in Live!

Get 1:1 Help Now