[mfastcom]

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

[Littlefoot]

What happens if you add a HAVING clause into your query? Such as

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;

[mfastcom]

Originally Posted by Littlefoot

What happens if you add a HAVING clause into your query? Such as

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

[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

[Pat Phelan]

I would suggest:

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

[Edadcon]

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

[mfastcom]

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.

[mfastcom]

Originally Posted by Pat Phelan

I would suggest:

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.