Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 Tags
Subject: Over-Killed Dupes in SQL Statement
Thread ID: 396048 Message ID: 396048 # Views: 78 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, January 22, 2014 2:29:01 PM         
   


I am seeing that in my effort to kills just a few dupes, about 10 or more, I killed off half the records that were supposed to get through, from over 3600 down to 1800+. So, my SQL JOIN statement somehow over-did it: 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.

How can I rewrite this so as not to over-do-it?

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


I think that the following is one way to do this; it may not be the best or most eloquent way to do this, but it seems to work.

SELECT GrpID, GrpNum, MAX(Eff_Date) AS Eff_Date FROM GRPEXTCV cv GROUP BY GrpID, GrpNum ;
	WHERE 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

SELECT DISTINCT cv.grpid, cv.grpnum, cv.bnf, cv.planKey, cv.Status ;
	FROM grpextcv cv ;
	JOIN tmpData td ON (cv.GrpID+cv.GrpNum=td.GrpID+td.GrpNum) AND ;
			cv.Eff_Date=td.Eff_Date ;
	WHERE ;
		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 tmpData2


Cecil Champenois, Jr.

ENTIRE THREAD

Over-Killed Dupes in SQL Statement Posted by Cecil Champenois @ 1/22/2014 2:29:01 PM
RE: Over-Killed Dupes in SQL Statement Posted by Tore Bleken @ 1/22/2014 3:04:05 PM
RE: Over-Killed Dupes in SQL Statement Posted by Jun Tangunan @ 1/23/2014 3:18:33 AM
RE: Over-Killed Dupes in SQL Statement Posted by Jun Tangunan @ 1/23/2014 3:18:39 AM
RE: Over-Killed Dupes in SQL Statement Posted by Cecil Champenois @ 1/23/2014 12:59:22 PM
RE: Over-Killed Dupes in SQL Statement Posted by Ali Koumaiha @ 1/23/2014 4:07:24 PM
RE: Over-Killed Dupes in SQL Statement Posted by Pete Sass @ 1/23/2014 4:07:46 PM
RE: Over-Killed Dupes in SQL Statement Posted by Cecil Champenois @ 1/23/2014 5:28:11 PM
RE: Over-Killed Dupes in SQL Statement Posted by Gene Wirchenko @ 1/23/2014 7:58:52 PM
RE: Over-Killed Dupes in SQL Statement Posted by Jun Tangunan @ 1/24/2014 12:05:00 AM
RE: Over-Killed Dupes in SQL Statement Posted by Anders Altberg @ 1/25/2014 1:31:47 AM
RE: Over-Killed Dupes in SQL Statement Posted by Christian Tabligan @ 1/25/2014 2:32:49 PM