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: 248997 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Saturday, December 26, 2009 12:55:56 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.



Finding the data corresponding to the lates date requires a correlated subquery):

SELECT * FROM Grades AS G1 WHERE date = (SELECT MAX(date) FROM Grades AS G2 WHERE G2.idno=G1.idno) INTO CURSOR Step1 

Finding the earliest (for contiguous period?) date where this grade first was given is the crux of the problem.
Try this:
SELECT G1.date, G2.* FROM Grades G1 JOIN Step1 G2 ON G1.idno=G2.idno ;
 WHERE G1.date=(SELECT MIN(date) FROM Grades G3 WHERE G3.idno=G2.idno AND G3.grade=G2.grade) ;
 INTO CURSOR Step2 


-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