Query Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Issue

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-11 : 16:36:50

/*I have two tables as illustrated below*/

Create Table TBLPosts(fDate datatime,amount money)
Create Table TBLInterest(StartDate datetime, EndDate DateTime, Interest dec)

--The dates can be any random dates, and same goes with the amounts
insert Into TBLPosts(fDate,amount)
Select '01/02/2006',20.00
Union
Select '02/05/2006',24.00
union
Select '03/01/2006',21.00
union
Select '04/02/2006',25.00

-- Again all dates can vary from user input along with the interest
Insert into TBLInterest(Startdate,EndDate,Interest)
Select '01/01/2006','03/31/2006',.05 -- 5%
union
select '04/01/2006','12/31/2006',.02 -- 2%

/* what I want to accomplish is the following. I want for each item in TBLPosts to be compared to the TBLInterest. These are the steps

1. Take the record from TblPosts(i.e '02/05/2006',24.00)
2. see if the date is between the start date and enddate for any of the items in TBL Interest.
3. If it is figure out the amount of month seperation between the start date and end date (i.e Take datediff(month,startdate,enddate), so I should end up with "3"
4. Figure out what month the record from tbl posts is from the start date (i.e 2)
5. since it is 2 out of 3 months they are only going to be getting 2/3 of that interest applied so I would take interest rate stated in tbleInterest (i.e 5%) and take 2/3's of that times the amount (i.e. (.05*(2/3))* 24.00)

This will give me the interest accumulated for that record.

Any Help you can give would be greatly appriciated. I know how to do this using a cursor, but I don't want to use that approach */

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-11 : 17:37:17

First - you need to declare the Interest column with some decimal places or your interest values are all going to be zero. Here is a query that will return the interest amount for each row in TBLPosts.

Create Table TBLPosts(fDate datetime,amount money)
Create Table TBLInterest(StartDate datetime, EndDate DateTime, Interest decimal(5,2))

--The dates can be any random dates, and same goes with the amounts
insert Into TBLPosts(fDate,amount)
Select '01/02/2006',20.00
Union
Select '02/05/2006',24.00
union
Select '03/01/2006',21.00
union
Select '04/02/2006',25.00

-- Again all dates can vary from user input along with the interest
Insert into TBLInterest(Startdate,EndDate,Interest)
Select '01/01/2006','03/31/2006',.05 -- 5%
union
select '04/01/2006','12/31/2006',.02 -- 2%

select P.fDate, P.amount, 
    I.Interest * P.amount * 
    (cast(datediff(m, P.fDate, I.EndDate) as money) 
    / cast(datediff(m, I.StartDate, I.EndDate) as money)) 
    AS InterestAmount
from TBLPosts P
inner join TBLInterest I on P.fDate between I.StartDate and I.EndDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources