Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Koen Piller
  Where is Koen Piller?
 Santpoort-Zuid
 Netherlands
 Koen Piller
 To: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 Tags
Subject: RE: SQL Statement-FoxPro doesn't like it!
Thread ID: 395523 Message ID: 395537 # Views: 43 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, January 15, 2014 6:32:50 PM         
   


> >
> >
> > This is wrong:
> >
> > JOIN (select MAX(Eff_Date) AS Eff_Date FROM GRPEXTCV GROUP BY Eff_Date) cv2 ;
> > 		ON cv.GrpID+cv.GrpNum=cv2.GrpID+cv2.GrpNum ;
> > 

> >
> > You select ONLY Eff_Date, but join the result by GrpID and GrpNum
> >
> > -----------------
> > Borislav Borissov
> >
> > Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
> > The only thing normal about database guys is their tables.
>
> Yes, you are correct. I saw, after I had posted the above, that I had left out fields which needed to be there in the secondary select statement.
> Here's the revised code:
>
> * Pull the data...
> * 01/15/2014 Cecil Champenois. Added code to also ensure that if there is more
> * than one record for the same insured, that we get the record having the latest
> * effective date. Added the JOIN statement and the first WHERE statement.
> SELECT DISTINCT cv.grpid, cv.grpnum, cv.bnf, cv.planKey, cv.Status ;
> 	FROM grpextcv cv ;
> 	JOIN (select GrpID, GrpNum, MAX(Eff_Date) AS Eff_Date FROM GRPEXTCV GROUP BY GrpID, GrpNum) cv2 ;
> 		ON cv.GrpID+cv.GrpNum=cv2.GrpID+cv2.GrpNum ;
> 	WHERE ;
> 		cv.Eff_Date=cv2.Eff_Date AND ;
> 		cv.Hist="99" AND ;
> 		cv.GrpID="APA" AND ;
> 		cv.PlanKey="DB1" AND ;
> 		(INLIST(cv.Status, "A", "B") OR ;
> 			(cv.Status="T" AND BETWEEN(cv.CHG_DATE, ldBegin, ldEnd))) ;
> 	ORDER BY cv.grpid, cv.grpnum ;
> 	INTO cursor tmpData
> 

>
> Cecil Champenois, Jr.


Cecil,

what is the result of your query? what did you expect?
Any change to give us sample date to reproduce?
Rgds,
Koen

ENTIRE THREAD

SQL Statement-FoxPro doesn't like it! Posted by Cecil Champenois @ 1/15/2014 6:04:44 PM
RE: SQL Statement-FoxPro doesn't like it! Posted by Borislav Borissov @ 1/15/2014 6:16:25 PM
RE: SQL Statement-FoxPro doesn't like it! Posted by Cecil Champenois @ 1/15/2014 6:19:03 PM
RE: SQL Statement-FoxPro doesn't like it! Posted by Koen Piller @ 1/15/2014 6:32:50 PM
RE: SQL Statement-FoxPro doesn't like it! Posted by Cecil Champenois @ 1/15/2014 6:50:02 PM