Linking Tables by Date Range

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Lewe22
    New Member
    • Sep 2007
    • 94

    Linking Tables by Date Range

    I have a table with a date stamp on it. [tblDistLearnTra ck]
    Code:
    unique_id      dist_created_date
    1              01/06/2008
    2              01/06/2008
    3              05/06/2008
    4              22/06/2008
    And a table with date ranges on it. [tblPayMonths]
    Code:
    month_id       start_date     end_date      pay_month
    07-APRMAY08    20/05/2008     19/06/2008    JUN-08 
    07-MAYJUN08    20/06/2008     19/07/2008    JUL-08
    07-JUNJUL08    20/07/2008     19/08/2008    AUG-08
    What i want to do is join the two tables together and pull in the relevant pay month where the created date falls in between the date ranges.
    So i am hoping to see results like this:
    Code:
    unique_id      dist_created_date      pay_month
    1              01/06/2008             JUN-08
    2              01/06/2008             JUN-08
    3              05/06/2008             JUN-08
    4              22/06/2008             JUL-08
    Does anyone have any ideas?
  • Stewart Ross
    Recognized Expert Moderator Specialist
    • Feb 2008
    • 2545

    #2
    One simple way to do this is to use a WHERE clause to select the relevant rows from the two otherwise-unjoined tables:

    Code:
    SELECT tblDistLearnTrack.unique_id, tblDistLearnTrack.dist_created_date, tblPayMonths.pay_month
    FROM tblDistLearnTrack, tblPayMonths
    WHERE (((tblDistLearnTrack.dist_created_date) Between [start_date] And [end_date]));
    which on your test data plus a couple of rows results in:

    Code:
    unique_id...dist_created_date......pay_month
    1..............01/06/2008............JUN-08
    2..............01/06/2008............JUN-08
    3..............05/06/2008............JUN-08
    4..............22/06/2008............JUL-08
    5..............30/07/2008............AUG-08
    6..............01/07/2008............JUL-08
    -Stewart

    Comment

    • Lewe22
      New Member
      • Sep 2007
      • 94

      #3
      Thanks Stewart. Knew it would be easy!

      Comment

      • Lewe22
        New Member
        • Sep 2007
        • 94

        #4
        Hang on a minute, that isn't working. It is omitting records....will post an example....

        Comment

        • Lewe22
          New Member
          • Sep 2007
          • 94

          #5
          My apologies, it did work!

          Comment

          • NeoPa
            Recognized Expert Moderator MVP
            • Oct 2006
            • 32633

            #6
            I would recommend using INNER JOIN within the FROM clause rather than the WHERE clause. This isn't critical, but it's clearer (for me at least) what is being done (SQL isn't easy to read at the best of times).
            Code:
            SELECT tDLT.unique_id,
                   tDLT.dist_created_date,
                   tPM.pay_month
            FROM tblDistLearnTrack AS tDLT INNER JOIN
                 tblPayMonths AS tPM
              ON tDLT.dist_created_date Between tPM.start_date And tPM.end_date

            Comment

            Working...