Results 1 to 3 of 3

Thread: How to do SQL one-to-many highest date selection

  1. #1
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    620
    Thanks
    274
    Thanked 14 Times in 14 Posts

    Default How to do SQL one-to-many highest date selection

    Scenario: Sales agents are assigned certain clients whom they must contact at least once every six months or the client reverts to a house client.

    Code:
    client table: client_id, name, agent, etc
    
    history table: client_id, actiondate, notes
    This is what I need to do but don't quite know how...

    Change the agent to HS in the client table if the agent hasn't contacted the client in 6 months. The history table holds multiple entries for each client with different dates. The agent changes to HS only if the most recent date for each client_id is more than 6 months ago. How do I address only the highest date for each client_id? Do I have to use a loop and do it one at a time? There are 36,000+ clients so it cannot be done manually.

    Any assistance would be much appreciated.

  2. #2
    Join Date
    Mar 2007
    Location
    Dublin, Ireland
    Posts
    23
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    If I understand your post correctly, I'd say you could make a selection, order it DESC and limiting it to 1:
    Code:
    SELECT * FROM history_table ORDER BY date DESC
    Also, put it in a custom loop and run it for all 36.000 records.

  3. The Following User Says Thank You to Butterfly For This Useful Post:

    kuau (09-22-2011)

  4. #3
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    620
    Thanks
    274
    Thanked 14 Times in 14 Posts

    Default

    Thanks very much! I had to use 2 WHILE loops but I believe it is doing what I needed it to do. Today is my birthday.. nice present. Mahalo, e

Bookmarks

Posting Permissions

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