Very Difficult Query, looks like I need a cursor.

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)

Very Difficult Query, looks like I need a cursor.

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-13 : 18:49:27

/*This is really difficult to explain, so I will do my best.

***I Have tried to clean this up. I can accomplish this w/ a cursor but I am trying to avoid doing that. Any sugestions would be greatly appriciated.

Below is a copy from a excel spreadsheet that shows how the contributions and the interest are applied. As illustrated below you should be able to see that the balance at the end of the previous year is carried over to the new year and interest is applied. The issue is all the line items that say begining balance do not exist and I need to somehow calcualte these.

The column with #'s that descend from 12 to 0 are x/12 of the interest rate to be applied.
x/12 Int.Rate Contr. Interest Earned
BeginningBalance 12 0.05 0 0
12/11/2003 11 0.05 100 4.583333333
1/11/2004 10 0.05 100 4.166666667
2/11/2004 9 0.05 100 3.75
3/11/2004 8 0.05 100 3.333333333
4/11/2004 7 0.05 100 2.916666667
5/11/2004 6 0.05 100 2.5
6/11/2004 5 0.05 100 2.083333333
7/11/2004 4 0.05 100 1.666666667
8/11/2004 3 0.05 100 1.25
9/11/2004 2 0.05 100 0.833333333
10/11/2004 1 0.05 100 0.416666667
11/11/2004 0 0.05 100 0
Total 1200 27.5 $1,227.50

BeginningBalance 12 0.04 1,227.50 49.1
12/11/2004 11 0.04 100 3.666666667
1/11/2005 10 0.04 100 3.333333333
2/11/2005 9 0.04 100 3
3/11/2005 8 0.04 100 2.666666667
4/11/2005 7 0.04 100 2.333333333
5/11/2005 6 0.04 100 2
6/11/2005 5 0.04 100 1.666666667
7/11/2005 4 0.04 100 1.333333333
8/11/2005 3 0.04 100 1
9/11/2005 2 0.04 100 0.666666667
10/11/2005 1 0.04 100 0.333333333
11/11/2005 0 0.04 100 0
Total 2,427.50 71.1 $2,498.60

BeginningBalance 12 0.03 2,498.60 74.958
12/11/2005 11 0.03 100 2.75
1/11/2006 10 0.03 100 2.5
2/11/2006 9 0.03 100 2.25
3/11/2006 8 0.03 100 2
4/11/2006 7 0.03 100 1.75
5/11/2006 6 0.03 100 1.5
6/11/2006 5 0.03 100 1.25
7/11/2006 4 0.03 100 1
8/11/2006 3 0.03 100 0.75
9/11/2006 2 0.03 100 0.5
10/11/2006 1 0.03 100 0.25
11/11/2006 0 0.03 100 0
Total 3,698.60 91.458 3,790.06

BeginningBalance 12 0.02 3,790.06 75.80116
12/11/2006 11 0.02 100 1.833333333
1/11/2007 10 0.02 100 1.666666667
2/11/2007 9 0.02 100 1.5
3/11/2007 8 0.02 100 1.333333333
4/11/2007 7 0.02 100 1.166666667
5/11/2007 6 0.02 100 1
6/11/2007 5 0.02 100 0.833333333
7/11/2007 4 0.02 100 0.666666667
8/11/2007 3 0.02 100 0.5
9/11/2007 2 0.02 100 0.333333333
10/11/2007 1 0.02 100 0.166666667
11/11/2007 0 0.02 100 0
4,990.06 86.80116 5,076.86

BeginningBalance 12 etc $5,076.86 etc

So in a working query I need someway to get that $5076.86 # which represents the balance.
*/

Create Table #Testtblwinterest(BenefitTypeID int,ReceivedDate datetime,Amount money, Interest decimal(5,2), InterestAmount money)

Create Table TSV_BenefitInterst(BenefitIntestestID,BenefitTypeID,StartDate Datetime,EndDate DateTime,Interest decimal(5,2))

Insert Into tsv_benefitInterest(BenefitInterestID,BenefitTypeID,StartDate,EndDate,Interest)
Select 4,3,'12/01/2006','11/30/2007',.02
union all
Select 3,3,'12/01/2005','11/30/2006',.03
union all
Select 2,3,'12/01/2004','11/30/2005',.04
union all
Select 1,3,'12/01/2003','11/30/2004',.05

Insert Into #TestTblWInterest(BenefitTypeID,ReceivedDate,Amount,Interest,InterestAmount)
select 3,'12/11/2003',100.00,.05,4.583
union All
select 3,'01/11/2004',100.00,.05,4.116
union All
select 3,'02/11/2004',100.00,.05,3.75
union All
select 3,'03/11/2004',100.00,.05,3.333
union All
select 3,'04/11/2004',100.00,.05,2.916
union All
select 3,'05/11/2004',100.00,.05,2.5
union All
select 3,'06/11/2004',100.00,.05,2.083
union All
select 3,'07/11/2004',100.00,.05,1.666
union All
select 3,'08/11/2004',100.00,.05,1.25
union All
select 3,'09/11/2004',100.00,.05,0.833
union All
select 3,'10/11/2004',100.00,.05,0.416
union All
select 3,'11/11/2004',100.00,.05,0
Union All
select 3,'12/11/2004',100.00,.04,3.666
Union All
select 3,'01/11/2005',100.00,.04,3.333
union All
select 3,'02/11/2005',100.00,.04,3
union All
select 3,'03/11/2005',100.00,.04,2.666
union All
select 3,'04/11/2005',100.00,.04,2.333
union All
select 3,'05/11/2005',100.00,.04,2
union All
select 3,'06/11/2005',100.00,.04,1.666
union All
select 3,'07/11/2005',100.00,.04,1.333
union All
select 3,'08/11/2005',100.00,.04,1
union All
select 3,'09/11/2005',100.00,.04,0.666
union All
select 3,'10/11/2005',100.00,.04,0.333
union All
select 3,'11/11/2005',100.00,.04,0
Union All
select 3,'12/11/2005',100.00,.03,2.75
Union All
select 3,'01/11/2006',100.00,.03,2.5
union All
select 3,'02/11/2006',100.00,.03,2.25
union All
select 3,'03/11/2006',100.00,.03,2
union All
select 3,'04/11/2006',100.00,.03,1.75
union All
select 3,'05/11/2006',100.00,.03,1.5
union All,
select 3,'06/11/2006',100.00,.03,1.25
union All
select 3,'07/11/2006',100.00,.03,1
union All
select 3,'08/11/2006',100.00,.03,0.75
union All
select 3,'09/11/2006',100.00,.03,0.5
union All
select 3,'10/11/2006',100.00,.03,0.25
union All
select 3,'11/11/2006',100.00,.03,0
Union All
select 3,'12/11/2006',100.00,.02,1.8333
Union All
select 3,'01/11/2007',100.00,.02,1.666
union All
select 3,'02/11/2007',100.00,.02,1.5
union All
select 3,'03/11/2007',100.00,.02,1.333
union All
select 3,'04/11/2007',100.00,.02,1.166
union All
select 3,'05/11/2007',100.00,.02,1
union All
select 3,'06/11/2007',100.00,.02,0.833
union All
select 3,'07/11/2007',100.00,.02,0.666
union All
select 3,'08/11/2007',100.00,.02,0.5
union All
select 3,'09/11/2007',100.00,.02,0.333
union All
select 3,'10/11/2007',100.00,.02,0.166
union All
select 3,'11/11/2007',100.00,.02,0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-15 : 17:24:07

Too much data, too cluttered.
Try with simpler sample data, and we will be able to comprehense what you are trying to do.

Peter Larsson
Helsingborg, Sweden

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-16 : 09:30:34

Believe me, I want to make it simpler and shorter, but I do not know any other way to. The reason I am showing four years of data (you can see in the spread sheet 12/11/2003 - 11/11/2007) is that I need to illustrate how the interest is applied at the beginning of each new year (as defined in the table labled TSV_BenefitInterest) to the entire balance in the account. I am trying to calculate the total balance in the account w/ the annual interest rate applied.

So if at the end of year 1 there is a total 1227.50 as illustrated above, when the interest rate for year 2 goes into effect I need to genereate interest on 1227.50 since that is everything that was in the account prior. If you look at the data above that I state was copied from a spreadsheet you should be able to see what I am trying to do. Pay attention to the Total column at the end of each year and how that amount is carried over to the beginning balance. I know this is very confusing, but I am completly stumped unless I use a cursor or do the programming in the front end application. Thanks in advance for any help.

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-16 : 10:45:43

So.....

You want a result set with a columnfor the amount, and a column with the balance? For each day ascedning by datetime?????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-16 : 11:07:21

Yes but the tricky part is if you look at the TSV_BenefitInterest Table you will see that there are beginning and end date ranges that have a interst rate associated with it.

I need each of those interest rates to be applied to the entire balance prior to the start date. So for example if I was looking at the following record in tsv_BenefitInterest from the first post on this topic

3,3,'12/01/2005','11/30/2006',.03

Basically if the date is prior to 12/01/2005 for the DateReceived then I need to apply the .03 interest rate to the entire balance. The part I am having the issue with is that when figuring out that balance to apply the .03 to, I need to factor in all previous interest the account would have generated from the two other records
(ie.
2,3,'12/01/2004','11/30/2005',.04
1,3,'12/01/2003','11/30/2004',.05 )

The best way to see exactly what i mean is to look at the data I copied into the original post from the spreadsheet, you will see how the amounts accumilate, and how the interest is applied.

Thanks

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-17 : 19:13:28

Solution : I used a cursor :(
Declare @BeginningBalance money,@TotalBalanceWoInterest money,@NewBalance Decimal(14,4)

set @BeginningBalance = 0
set @TotalBalanceWoInterest = 0
set @NewBalance = 0

Declare @PreviousID Int
set @PreviousID = 0

Declare CName cursor for
Select * from #Testtblwinterest a where a.BenefitInterestID <> 0
order by a.DateReceived,a.BenefitInterestID
open Cname
fetch next from CName
into into @BenefitInterestID,@BenefitTypeID,@DateReceived,@Amount,@Interest, @InterestAmount
While @@Fetch_Status = 0
Begin
If (@PreviousID = 0)
Begin
set @NewBalance = @TotalBalanceWoInterest
End
if (@PreviousID <> @BenefitInterestID)
Begin
Set @PreviousID = @BenefitInterestID
set @NewBalance = (@NewBalance + (cast(@NewBalance as decimal(14,4))) * @Interest)
set @NewBalance = @NewBalance + @InterestAmount + cast(@Amount as decimal(14,4))
end
Else
Begin
set @NewBalance = @NewBalance + @InterestAmount + cast(@Amount as decimal(14,4))
End
fetch next from CName
into @BenefitInterestID,@BenefitTypeID,@DateReceived,@Amount,@Interest, @InterestAmount
end
close Cname
deallocate Cname

Begin
set @NewBalance = @NewBalance + (Select cast(isnull(sum(a.Amount),0) as Money) from #TmpTblInterest a
where a.BenefitInterestID = 0)
End
select cast(@NewBalance as decimal(10,2))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 04:13:50

See this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78780

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources