Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Rahul Moudgill
  Where is Rahul Moudgill?
 Toronto
 Canada
 Rahul Moudgill
 To: Frank Cazabon
  Where is Frank Cazabon?
 
 Trinidad And Tobago
 Frank Cazabon
 Tags
Subject: RE: Get Effective Rate
Thread ID: 365790 Message ID: 365793 # Views: 74 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Monday, December 31, 2012 2:05:34 PM         
   


> Hi,
>
> I am trying to write some code to ensure the correct effective rate is being used for a calculation. Here's some sample code:
>
>
CREATE CURSOR c_Coverage;
> (cov_pk int,;
> cov_desc c(10))
> 
> INSERT INTO c_Coverage ;
> VALUES (1, "Coverage1")
> 
> INSERT INTO c_Coverage ;
> VALUES (2, "Coverage2")
> 
> CREATE CURSOR c_CoverageRates;
> (rat_pk int,;
> rat_covfk int,;
> rat_Rate n(10,2),;
> rat_date d)
> 
> INSERT INTO c_CoverageRates;
> values (1, 1, 5, DATE(2009,6,1))
> 
> INSERT INTO c_CoverageRates;
> values (2, 1, 10, DATE(2010,1,1))
> 
> INSERT INTO c_CoverageRates;
> values (3, 1, 15, DATE(2011,1,1))
> 
> INSERT INTO c_CoverageRates;
> values (4, 2, 20, DATE(2010,1,1))
> 
> m.ldPeriodStart = DATE(2010, 6, 1)
> m.ldPeriodEnd = GOMONTH(m.ldPeriodStart, 1) - 1
> 
> SELECT cov_desc, rat_rate, rat_date;
> FROM c_Coverage ;
> 	INNER JOIN c_CoverageRates ON rat_covfk = cov_pk;
> 	ORDER BY cov_desc, rat_date/vfp>
> 
> This will give me this result:
> 
> 
> Cov_desc  Rat_rate   Rat_date  
> Coverage1       5.00 01/06/2009
> Coverage1      10.00 01/01/2010
> Coverage1      15.00 01/01/2011
> Coverage2      20.00 01/01/2010
> 

>
> Now I need to get this, since the month I am selecting for (defined by m.ldPeriodStart and m.ldPeriodEnd) is June 2010:
>
>
Cov_desc  Rat_rate   Rat_date  
> Coverage1      10.00 01/01/2010
> Coverage2      20.00 01/01/2010

>
> I know I've done this before but must have had too much celebrations over the last week!
>
> Frank.
>
> Frank Cazabon
> Samaan Systems Ltd.
> www.SamaanSystems.com

CREATE CURSOR c_Coverage;
(cov_pk int,;
cov_desc c(10))

INSERT INTO c_Coverage ;
VALUES (1, "Coverage1")

INSERT INTO c_Coverage ;
VALUES (2, "Coverage2")

CREATE CURSOR c_CoverageRates;
(rat_pk int,;
rat_covfk int,;
rat_Rate n(10,2),;
rat_date d)

INSERT INTO c_CoverageRates;
values (1, 1, 5, DATE(2009,6,1))

INSERT INTO c_CoverageRates;
values (2, 1, 10, DATE(2010,1,1))

INSERT INTO c_CoverageRates;
values (3, 1, 15, DATE(2011,1,1))

INSERT INTO c_CoverageRates;
values (4, 2, 20, DATE(2010,1,1))

m.ldPeriodStart = DATE(2009, 6, 1)
m.ldPeriodEnd = GOMONTH(m.ldPeriodStart, 12) - 1

SELECT MAX(rat_pk) as rat_pk,rat_covfk ;
	FROM c_CoverageRates ;
	WHERE BETWEEN(c_CoverageRates.rat_date,m.ldPeriodStart,m.ldPeriodEnd) ;
	GROUP BY rat_covfk ;
	INTO CURSOR tmp

SELECT cov_desc, rat_rate, rat_date;
FROM c_Coverage ;
	INNER JOIN c_CoverageRates ON rat_covfk = cov_pk ;
	WHERE c_CoverageRates.rat_pk in (SELECT tmp.rat_pk FROM tmp) ;
	ORDER BY cov_desc, rat_date


Rahul

ENTIRE THREAD

Get Effective Rate Posted by Frank Cazabon @ 12/31/2012 1:39:24 PM
RE: Get Effective Rate Posted by Rahul Moudgill @ 12/31/2012 2:05:34 PM
RE: Get Effective Rate Posted by Frank Cazabon @ 12/31/2012 2:22:14 PM
RE: Get Effective Rate Posted by Cetin Basoz @ 12/31/2012 2:06:32 PM
RE: Get Effective Rate Posted by Frank Cazabon @ 12/31/2012 2:25:31 PM
RE: Get Effective Rate Posted by Cetin Basoz @ 12/31/2012 2:34:12 PM
RE: Get Effective Rate Posted by DEREK DODOO @ 3/28/2013 9:27:48 PM
RE: Get Effective Rate Posted by Cetin Basoz @ 3/28/2013 10:23:52 PM
RE: Get Effective Rate Posted by Frank Cazabon @ 3/29/2013 5:25:49 PM
RE: Get Effective Rate Posted by Frank Cazabon @ 3/29/2013 9:07:35 PM
RE: Get Effective Rate Posted by DEREK DODOO @ 3/30/2013 5:46:55 PM
RE: Get Effective Rate Posted by Frank Cazabon @ 3/30/2013 6:04:36 PM
RE: Get Effective Rate Posted by Anders Altberg @ 12/31/2012 5:30:38 PM
RE: Get Effective Rate Posted by Frank Cazabon @ 12/31/2012 7:15:13 PM