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: 248990 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Saturday, December 26, 2009 10:10:18 AM         
   


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

SET DATE BRITISH
CREATE CURSOR TMP (IDNO C(4), DATE d, GRADE C(2), LEVEL i, AMOUNT n(12,0))
INSERT INTO tmp VALUES ("0001", CTOD("11/11/01"), "AM", 1, 200)
INSERT INTO tmp VALUES ("0001", CTOD("12/11/02"), "AM", 2, 220)
INSERT INTO tmp VALUES ("0001", CTOD("14/11/03"), "AM", 3, 250)

INSERT INTO tmp VALUES ("0001", CTOD("11/11/05"), "SM", 1, 400)
INSERT INTO tmp VALUES ("0001", CTOD("12/11/06"), "SM", 2, 460)
INSERT INTO tmp VALUES ("0001", CTOD("13/11/07"), "SM", 3, 570)

INSERT INTO tmp VALUES ("0002", CTOD("11/11/01"), "CM", 1, 1100)
INSERT INTO tmp VALUES ("0002", CTOD("12/11/02"), "CM", 2, 1220)
INSERT INTO tmp VALUES ("0002", CTOD("13/11/03"), "CM", 3, 1250)

INSERT INTO tmp VALUES ("0002", CTOD("11/11/05"), "HE", 1, 1500)
INSERT INTO tmp VALUES ("0002", CTOD("12/11/06"), "HE", 2, 1660)
INSERT INTO tmp VALUES ("0002", CTOD("13/11/07"), "HE", 3, 1870)

Select idno, min(date) As min_date, Grade, Max(date) as Max_Date,  min(Amount) as min_amt, max(amount) as max_amt ;
From tmp ;
where idno + grade in (Select idno + Max(Grade) From tmp Group By idno) ;
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