Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Frank Cazabon
  Where is Frank Cazabon?
 
 Trinidad And Tobago
 Frank Cazabon
 To: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 Tags
Subject: RE: Get Effective Rate
Thread ID: 365790 Message ID: 365798 # Views: 57 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Monday, December 31, 2012 2:25:31 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
>
> Frank,
> First it shouldn't be a period but a single date, no? If a period then you should calculate for each date in that period, if you ask why, effective date might have changed within that period too.
>
> With a single date:
>
>
Select cov_desc, rat_Rate, rat_date;
> 	FROM c_Coverage ;
> 	INNER Join c_CoverageRates On rat_covfk = cov_pk;
> 	where rat_date = (select Max(rat_Date) from c_CoverageRates where c_CoverageRates.Rat_date <= m.ldDate) ;
>         ORDER BY cov_desc

>
> With a period:
>
>
Create Cursor periodDates (dDate d)
> For ix = 0 To m.ldPeriodEnd-m.ldPeriodStart
> 	Insert Into periodDates Values (m.ldPeriodStart+m.ix)
> Endfor
> 
> Select cov_desc, rat_Rate, rat_date, dDate;
> 	FROM c_Coverage ;
> 	INNER Join c_CoverageRates On rat_covfk = cov_pk;
> 	inner join ;
> 	( Select dDate,rat_covfk as ratCovFK, Max(rat_date) as ratDate ;
>   From c_CoverageRates, periodDates ;
>   Where c_CoverageRates.rat_date <= periodDates.dDate ;
>   group by dDate,rat_covfk ) efRates ;
>   on c_CoverageRates.Rat_covfk = efRates.RatCovFK and efRates.ratDate = c_CoverageRates.Rat_date ;
>   order by cov_desc, dDate

>
>
>

> Cetin Basoz
>
> .Net has got better.Think about moving - check my blog:
> My Blog
> Blog (mirror) - sounds to be down
>

> Support Wikipedia

Thanks Cetin,

in the meantime my memory came back and I came up with this:

SELECT cov_desc, rat_rate, c_CoverageRates.rat_date;
FROM (SELECT rat_covfk, MAX(rat_date) as rat_date ;
		FROM c_CoverageRates ;
		WHERE rat_date <= m.ldPeriodEnd;
		GROUP BY rat_covfk) rates;
	INNER JOIN c_CoverageRates ON rates.rat_covfk = c_CoverageRates.rat_covfk AND rates.rat_date = c_CoverageRates.rat_date;
	INNER JOIN c_Coverage ON cov_pk = rates.rat_covfk


You are right about the period and that may cause a problem in the future but for now I'll stick with just the end date.

Do you see any advantage/disadvantage of your solution over mine?

This is actually going to run on a SQL Server database.

Frank.

Frank Cazabon
Samaan Systems Ltd.
www.SamaanSystems.com

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