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
 To: M. Akram Bhatti
  Where is M. Akram Bhatti?
 Riyadh
 Saudi Arabia
 M. Akram Bhatti
 Tags
Subject: RE: select max & min for the latest grade
Thread ID: 248987 Message ID: 248989 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Saturday, December 26, 2009 9:44:51 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


The minimum date selected will not be correct.

I've enhanced the question, since you answered.

Pls assist

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