Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2002
    Posts
    10

    SQL Latest Date Query

    Guys,

    currently Ive got the following SQL query:

    SELECT DISTINCT
    AGNTS.NAME,
    AG_CDR.END_BAL,
    Max(AG_CDR.BENDDATE) AS MaxOfBENDDATE
    FROM AGNTS
    INNER JOIN AG_CDR ON AGNTS.AGENT = AG_CDR.AGENT_ID
    GROUP BY
    AGNTS.NAME, AG_CDR.END_BAL
    ORDER BY
    AGNTS.NAME, Max(AG_CDR.BENDDATE) DESC;

    the query produces result set in the following manner:

    NAME END_BAL MaxOfBENDDATE
    BetaCom 7.911614836 14/09/2000 08:55:05
    BetaCom 7.913049836 14/09/2000 08:52:06
    BetaCom 7.916126218 14/09/2000 08:42:50
    DualBand 1655.333041 19/04/2001 15:44:16
    DualBand 1655.350061 19/04/2001 15:42:56
    DualBand 1655.367081 19/04/2001 15:21:45
    ....Co 3
    ....Co 3
    ....Co 3
    ....Co 4
    ....Co 4
    ....Co 4


    however, what I really want is for the query to only display
    only single record per company name that displays END_BAL with the most recent BENDDATE. The desired output should look like the one below:

    NAME END_BAL MaxOfBENDDATE
    BetaCom 7.911614836 14/09/2000 08:55:05
    DualBand 1655.333041 19/04/2001 15:44:16
    ....Co 3
    ....Co 4
    ....Co 5

    Any help would be highly appreciated

    Thanks in Advance

    Mark

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,005
    What happens if you add a HAVING clause into your query? Such as
    Code:
    SELECT ag.NAME, ac.end_bal, ac.benddate
               FROM agnts ag INNER JOIN ag_cdr ac ON ag.AGENT = ac.agent_id
           GROUP BY ag.NAME, ac.end_bal, ac.benddate
             HAVING ac.benddate = (SELECT MAX (ac1.benddate)
                                     FROM ag_cdr ac1
                                    WHERE ac1.end_bal = ac.end_bal)
           ORDER BY ag.NAME, ac.benddate DESC;

  3. #3
    Join Date
    Jul 2002
    Posts
    10
    Quote Originally Posted by Littlefoot
    What happens if you add a HAVING clause into your query? Such as
    Code:
    SELECT ag.NAME, ac.end_bal, ac.benddate
               FROM agnts ag INNER JOIN ag_cdr ac ON ag.AGENT = ac.agent_id
           GROUP BY ag.NAME, ac.end_bal, ac.benddate
             HAVING ac.benddate = (SELECT MAX (ac1.benddate)
                                     FROM ag_cdr ac1
                                    WHERE ac1.end_bal = ac.end_bal)
           ORDER BY ag.NAME, ac.benddate DESC;
    Hi
    thanks for the input, however there was no change in the output...
    Any other ideas?
    Mark

  4. #4
    Join Date
    Oct 2004
    Posts
    13

    SCORE BY Last date for agents

    Hi,
    Try this one, it worked fine in my test database

    SELECT a.name, ag.END_BAL, ag.BENDDATE
    FROM agnts AS a, AG_CDR AS ag
    WHERE a.agent=ag.AGENT_ID
    AND ag.BENDDATE IN
    (SELECT MAX(BENDDATE)FROM AG_CDR GROUP BY AGENT_ID);

    (maybe you have to correct some names I didn't had your tables)
    Best regards
    Robert

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,910
    I would suggest:
    Code:
    SELECT 
       AGNTS.NAME
    ,  AG_CDR.END_BAL
    ,  AG_CDR.BENDDATE
       FROM AGNTS 
       INNER JOIN AG_CDR
          ON AGNTS.AGENT = AG_CDR.AGENT_ID 
       WHERE  AG_CDR.BENDATE = (SELECT Max(z.BENDATE)
          FROM AG_CDR AS z
          WHERE  z.AGENT_ID = AG_CDR.AGENT_ID)
       ORDER BY AGNTS.NAME;
    -PatP

  6. #6
    Join Date
    Oct 2004
    Posts
    13

    Talking

    Hi Pat,
    Good solution too.
    Always nice to see there are more solutions to one question
    B.R.
    Robert

  7. #7
    Join Date
    Jul 2002
    Posts
    10
    Quote Originally Posted by Edadcon
    Hi,
    Try this one, it worked fine in my test database

    SELECT a.name, ag.END_BAL, ag.BENDDATE
    FROM agnts AS a, AG_CDR AS ag
    WHERE a.agent=ag.AGENT_ID
    AND ag.BENDDATE IN
    (SELECT MAX(BENDDATE)FROM AG_CDR GROUP BY AGENT_ID);

    (maybe you have to correct some names I didn't had your tables)
    Best regards
    Robert

    Thanks for contributing... the script was a major improvement however for some reason some companies were still showing multiple records (ie most recent record and the one before)... very strange...
    ...anyhow thanks for the effort...
    M.

  8. #8
    Join Date
    Jul 2002
    Posts
    10

    Smile

    Quote Originally Posted by Pat Phelan
    I would suggest:
    Code:
    SELECT 
       AGNTS.NAME
    ,  AG_CDR.END_BAL
    ,  AG_CDR.BENDDATE
       FROM AGNTS 
       INNER JOIN AG_CDR
          ON AGNTS.AGENT = AG_CDR.AGENT_ID 
       WHERE  AG_CDR.BENDATE = (SELECT Max(z.BENDATE)
          FROM AG_CDR AS z
          WHERE  z.AGENT_ID = AG_CDR.AGENT_ID)
       ORDER BY AGNTS.NAME;
    -PatP
    Pat, guess what, the script worked a treat!!! well done!! Cheers...
    Many thanks for all who contributed...
    M.

Posting Permissions

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