Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Unanswered: Working with single effective date

    I have a table with a single effective date, rather than both a start and stop date. I have to be able to match up this table to another one with service information in it and am not sure how to get the correct record selected.

    So in table one I have a personID, effective date, and lots of other fields. There are also multiple records for each personID, so say personID 1 has records with effective dates of 1/1/2007, 6/1/2007, and 1/1/2008.

    Table two has personID, Service Date, and lots of other fields.

    So if I am looking to match up the effective row from table one to a record in table two with a service date of 8/1/2007, how do I get the db to locate and return the record with an effective date of 6/1/2007, and only this record?

  2. #2
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    I know that I need code that will select the maximum value that is less than the service date, but cannot figure out how to do that. I have tried sub-queries, coorelated queries, etc. but they all pull either all of the effective date values that are less than the service date or pull nothing. I can't believe how much trouble I'm having with this as it has to be a pretty common thing?

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    If this does not shed some light on the problem, please see the instruction on the sticky at the top of this message board.

    SELECT TOP 1 t1.personID
    FROM table1 t1
    JOIN table2 t2
    ON t1.personID = t2.personID
    WHERE t1.effectivedate > t2.servicedate
    ORDER BY t1.effectivedate DESC
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    That (appears) to work to pull a single item, but I need to pull the correct effective date for every entry in the service,, and there are thousands of them.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Code:
    SELECT t1.personID, MAX(t1.effectivedate)
    FROM table1 t1
    JOIN table2 t2
    ON t1.personID = t2.personID
    WHERE t1.effectivedate > t2.servicedate
    GROUP BY t1.personid
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    That code I have tried, even a little more advanced and it pulls multiple entries for each service date. If there are three effective dates and two of them meet the criteria then both are pulled, when in fact no more than one effective date can be correct.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you really want help, start here. For starters though, try:
    Code:
    SELECT *
       FROM [Table two] AS d
       JOIN [Table one] AS s
          ON (s.[Person ID] = d.[Person ID]
          AND s.[effective date] = (SELECT Max(z.[effective date])
             FROM [Table one] AS z
             WHERE  z.[Person ID] = d.[Person ID]
                AND z.[effective date] < d.[service date]))
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •