If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > SQL Latest Date Query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,985
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;
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,735
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On