Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: tushar Kanvinde
  Where is tushar Kanvinde?
 Kolhapur, Maharastra
 India
 tushar Kanvinde
 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: 248999 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Saturday, December 26, 2009 1:54:52 PM         
   


Try

CREATE CURSOR TMP (IDNO C(4), DATE d, GRADE C(2), LEVEL i, AMOUNT n(12,0))
INSERT INTO tmp VALUES ("0001", {^2001/11/11}, "AM", 1, 200)
INSERT INTO tmp VALUES ("0001", {^2002/11/12}, "AM", 2, 220)
INSERT INTO tmp VALUES ("0001", {^2003/11/14}, "AM", 3, 250)

INSERT INTO tmp VALUES ("0001", {^2005/11/11}, "SM", 1, 400)
INSERT INTO tmp VALUES ("0001", {^2006/11/12}, "SM", 2, 460)
INSERT INTO tmp VALUES ("0001", {^2007/11/13}, "SM", 3, 570)

INSERT INTO tmp VALUES ("0002", {^2001/11/11}, "CM", 1, 1100)
INSERT INTO tmp VALUES ("0002", {^2002/11/12}, "CM", 2, 1220)
INSERT INTO tmp VALUES ("0002", {^2003/11/13}, "CM", 3, 1250)

INSERT INTO tmp VALUES ("0002", {^2005/11/11}, "HE", 1, 1500)
INSERT INTO tmp VALUES ("0002", {^2006/11/12}, "HE", 2, 1660)
INSERT INTO tmp VALUES ("0002", {^2007/11/13}, "HE", 3, 1870)


SELECT MAX(DATE) as DATE,IDNO FROM tmp GROUP BY IDNO INTO CURSOR Temp
SELECT TEMP.*,tmp.GRADE FROM TEMP LEFT JOIN tmp ON tmp.IDNO=TEMP.IDNO and tmp.DATE=TEMP.DATE INTO CURSOr TEMP1

SELECT tmp.IDNO,MIN(tmp.DATE),tmp.GRADE, MAX(tmp.DATE), MAX(tmp.AMOUNT),MIN(tmp.AMOUNT) FROM tmp LEFT JOIN temp1 ON temp1.idno=tmp.idno AND temp1.grade=tmp.grade WHERE temp1.grade is NOT null GROUP BY tmp.idno,tmp.grade


Regards
Tushar

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