Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Jun Tangunan
  Where is Jun Tangunan?
 Cabanatuan
 Philippines
 Jun Tangunan
 To: Russell Hill
  Where is Russell Hill?
 Sydney
 Australia
 Russell Hill
 Tags
Subject: RE: SQL problem
Thread ID: 330678 Message ID: 331035 # Views: 32 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Monday, December 26, 2011 4:08:53 AM         
   


> >
> > Russel, try this:
> >
> >
> > Set Date BRITISH
> > 
> > Create Cursor testCursor ;
> > 	(TRANSDATE D(8), ;
> > 	TRANSNAME C(20), ;
> > 	TRANSTYPE N(2,0), ;
> > 	TRANSMARK N(6,4), ;
> > 	TRANSDAY C(5))
> > Insert Into testCursor Values (Ctod('10/09/10'),'Adams',4,0.3456,'')
> > Insert Into testCursor Values (Ctod('23/12/10'),'Jones',5,0.2510,'')
> > Insert Into testCursor Values (Ctod('11/11/10'),'Adams',1,0.1111,'')
> > Insert Into testCursor Values (Ctod('16/01/11'),'Jones',7,0.1818,'')
> > Insert Into testCursor Values (Ctod('10/11/11'),'Smith',2,0.3333,'')
> > Insert Into testCursor Values (Ctod('23/02/11'),'Jones',5,0.1000,'')
> > Insert Into testCursor Values (Ctod('16/04/11'),'Adams',1,0.1313,'')
> > Insert Into testCursor Values (Ctod('12/07/10'),'Jones',1,0.4444,'')
> > Insert Into testCursor Values (Ctod('07/05/11'),'Smith',1,0.5000,'')
> > Insert Into testCursor Values (Ctod('22/10/10'),'Smith',6,0.6667,'')
> > Insert Into testCursor Values (Ctod('04/03/11'),'Adams',3,0.1250,'')
> > Insert Into testCursor Values (Ctod('13/02/11'),'Jones',1,0.2000,'')
> > 
> > 
> > Select t1.TRANSDATE, Padl(Transform(t1.TRANSDATE-Ctod('01/01/85')),5,'0') As TRANSDAY,;
> > 	t1.TRANSNAME, t1.TRANSTYPE, t1.TRANSMARK, ;
> > 	cast(ROUND(Sum(Iif(t2.TRANSTYPE=1,1,0))/Count(*)*100,2) As n(8,4)) CUMUPERC,;
> > 	CAST(Nvl(Sum(t2.TRANSMARK),0) As N(6,4)) As cumutrans From testCursor t1;
> > 	LEFT Join testCursor t2;
> > 	ON t2.TRANSDATE < t1.TRANSDATE And t2.TRANSNAME = t1.TRANSNAME;
> > 	GROUP By 1,2,3,4,5;
> > 	ORDER By 3,1
> > 

> >
> >
> > Just one advice, it is better to use the DATE() function or {^} than CTOD(). ;-)
> >
> >
> > http://sandstorm36.blogspot.com
> > http://weblogs.foxite.com/sandstorm36/default.aspx
>
> That's very nice Jun - thanks a lot.
>
> It works beautifully in the test case and you have empirically proven its superiority in speed to the SCAN.
>
> I agree with you that the speed advantage would be even greater when running both against my large table.
>
> Unfortunately when I ran it I got the following error after about 15 minutes of processing:
>
>
>
> That seems at a glance like one of those horrible crashes that would be difficult to fix.
>
> Regards,
>
> Russell.


Would grouping possible and faster?

* Create an empty cursor 
Select t1.TRANSDATE, chr(5) As TRANSDAY, t1.TRANSNAME, t1.TRANSTYPE, t1.TRANSMARK, ;
	cast(0 As N(8,4)) CUMUPERC,;
	CAST(0 As N(6,4)) As CUMUTRANS From testCursor t1;
	WHERE .F. Into Cursor junkrep Readwrite
	
* work on all letters one at a time 	
For lnloop = 65 To 90
    * Get the current group, e,g, A, B, C....
	Select * From sourcetable Where upper(Left(TRANSNAME,1)) = Chr(m.lnloop) Into Cursor testcursor NOFILTER
	
	Select t1.TRANSDATE, Padl(Transform(t1.TRANSDATE-Ctod('01/01/85')),5,'0') As TRANSDAY,;
		t1.TRANSNAME, t1.TRANSTYPE, t1.TRANSMARK, ;
		cast(Round(Sum(Iif(t2.TRANSTYPE=1,1,0))/Count(*)*100,2) As N(8,4)) CUMUPERC,;
		CAST(Nvl(Sum(t2.TRANSMARK),0) As N(6,4)) As CUMUTRANS From testCursor t1;
		LEFT Join testCursor t2;
		ON t2.TRANSDATE < t1.TRANSDATE And t2.TRANSNAME = t1.TRANSNAME;
		GROUP By 1,2,3,4,5;
		ORDER By 3,1 INTO CURSOR junkrep2
		SELECT junkrep 
		APPEND FROM DBF("junkrep2")
Next
USE IN SELECT("junkrep2")
SELECT junkrep 
BROWSE NORMAL 


Or something like that? The goal is to process records on smaller volume (one letter at a time).

http://sandstorm36.blogspot.com
http://weblogs.foxite.com/sandstorm36/default.aspx

ENTIRE THREAD

SQL problem Posted by Russell Hill @ 12/22/2011 1:35:41 AM
RE: SQL problem Posted by tushar @ 12/22/2011 8:05:13 AM
RE: SQL problem Posted by Russell Hill @ 12/22/2011 11:15:31 PM
RE: SQL problem Posted by tushar @ 12/23/2011 5:25:39 AM
RE: SQL problem Posted by Jun Tangunan @ 12/23/2011 7:44:54 AM
RE: SQL problem Posted by Russell Hill @ 12/23/2011 10:13:15 AM
RE: SQL problem Posted by tushar @ 12/23/2011 3:32:35 PM
RE: SQL problem Posted by Anders Altberg @ 12/22/2011 12:31:06 PM
RE: SQL problem Posted by Russell Hill @ 12/22/2011 11:14:02 PM
RE: SQL problem Posted by Jun Tangunan @ 12/23/2011 7:29:56 AM
RE: SQL problem Posted by Russell Hill @ 12/23/2011 10:16:50 AM
RE: SQL problem Posted by Jun Tangunan @ 12/26/2011 4:08:53 AM