Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 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: 248992 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Saturday, December 26, 2009 12:14:41 PM         
   


> 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.



May I ask if there are functional depndencies between the columns in this table? Is there only ever one grade given per level? Does the amount always increase for each increased level?
Can the grade change from say SM to HE then back to SM again on three different dates, for one and the same person? If the real data can be more complex than your sample it will require different queries.
-Anders

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