Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: M. Akram Bhatti
  Where is M. Akram Bhatti?
 Riyadh
 Saudi Arabia
 M. Akram Bhatti
 To: Leonard Trevor
  Where is Leonard Trevor?
 Edinburgh
 United Kingdom
 Leonard Trevor
 Tags
Subject: RE: select max & min for the latest grade
Thread ID: 248987 Message ID: 248988 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Saturday, December 26, 2009 9:32:35 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 max_DATE
> -------------------------------------------
>
> 0001 11/11/05 13/11/07
>
> 0002 11/11/05 13/11/07
>
> 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.

SELECT IDNo, Grade, Min(Date) AS MinDate, Max(Date) AS MaxDate, Level, Amount ;
FROM YourTable Group By IDNo, Grade

Regards,
akram

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