Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Leonard Trevor
  Where is Leonard Trevor?
 Edinburgh
 United Kingdom
 Leonard Trevor
 Tags
Subject: select max & min for the latest grade
Thread ID: 248987 Message ID: 248987 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Saturday, December 26, 2009 9:24:46 AM         
   


Dear Expert,

A select statement to select the maximum amount & minimun amount for each individual on his

current or latest grade as follows::


IDNO DATE GRADE LEVEL AMOUNT
---------------------------------------------------------------
0001 11/11/01 AM 1 200
0001 12/11/02 AM 2 220
0001 14/11/03 AM 3 250

0001 11/11/05 SM 1 400
0001 12/11/06 SM 2 460
0001 13/11/07 SM 3 570

0002 11/11/01 CM 1 1100
0002 12/11/02 CM 2 1220
0002 13/11/03 CM 3 1250

0002 11/11/05 HE 1 1500
0002 12/11/06 HE 2 1660
0002 13/11/07 HE 3 1870



EXPECTED OUTPUT
----------------
IDNO min_ DATE GRADE min_DATE max_AMT min_AMT
------------------------------------------------------------------------

0001 11/11/05 SM 13/11/07 400 570

0002 11/11/05 HE 13/11/07 1500 1870

note::
------

idno='0001' has been on grade 'AM' earlier by date but his current grade is 'SM',

which is the latest by date.


Please provide a select statement to accomplish the above.

Thanks.

ENTIRE THREAD

select max & min for the latest grade Posted by Leonard Trevor @ 12/26/2009 9:24:46 AM
RE: select max & min for the latest grade Posted by M. Akram Bhatti @ 12/26/2009 9:32:35 AM
RE: select max & min for the latest grade Posted by Leonard Trevor @ 12/26/2009 9:44:51 AM
RE: select max & min for the latest grade Posted by M. Akram Bhatti @ 12/26/2009 10:10:18 AM
RE: select max & min for the latest grade Posted by Leonard Trevor @ 12/26/2009 12:15:00 PM
RE: select max & min for the latest grade Posted by Anders Altberg @ 12/26/2009 12:14:41 PM
RE: select max & min for the latest grade Posted by Anders Altberg @ 12/26/2009 12:55:56 PM
RE: select max & min for the latest grade Posted by tushar @ 12/26/2009 1:54:52 PM
RE: select max & min for the latest grade Posted by Anders Altberg @ 12/26/2009 6:50:13 PM
RE: select max & min for the latest grade Posted by Anders Altberg @ 12/26/2009 4:29:10 PM