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.
| Author |
Topic |
|
ntn104
Posting Yak Master
175 Posts |
 Posted - 2010-03-10 : 12:11:11
|
| Hi everyone,I need to know how to write sql statement to calculate tax interest base on due date of each quarter. For example:1st quarter due on april 312nd quarter due on july 313rd quarter due on october 30and 4th quarter due on jan 31Example:(1) taxbalance=1657.32, and interest rate is 1% per month. Effectivedate is 06/24/1997.(2) taxbalance=0, and interest rate is 1% for latepayment...and effective date is 1/22/2010thank you, |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-10 : 12:39:03
|
| how is data held in your table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
ntn104
Posting Yak Master
175 Posts |
Posted - 2010-03-10 : 13:00:07
|
quote:
Originally posted by visakh16
how is data held in your table?accountno (varchar 11)taxperiod (varchar 5 - 4 digit for year and 5 digit for quarter)casenum (varchar 7taxdue (decimal 11,2)intdue (decimal 11,2)pendue (decimal 11,2)balancedue (decimal 11,2)but on the table the interst colum and balance due is not calculate up to date...so when I run report, i don't get the right amount of interst and balance. For example:accountno = 02044632800taxperiod = 19962 (2nd quarter of year 1996)casenum=8007326taxdue=1657.32intdue= 132.59 (but it suppose to list 2718.01 as of today)legdue=0pendue=165.73balancedue=2021.93 (but it suppose to be $4541.06Thanks,------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-03-10 : 16:00:52
|
| ntn how are you calculating interest? And what is your starting amount.I used 132.59 as the base amount.And then used...Select DateDiff(mm,'4/1/1996',GetDate()) = 167 months (I assumed this for 2nd qtr 1996)Then did 132.59 * ( 1.01 ^ (167)) to calculate interest and only got $698.52.I even added another % to assume all had late charges.132.59 * ( 1.02 ^ (167)) and got $3,680.02, which is also not your value.The closes I got was at 152 mo. overdue...So help me on this equation here :)Edit:Unless they got 167 monthly interest charges, and then 55 quarterly interest charges compounded on that. |
|
|
|
ntn104
Posting Yak Master
175 Posts |
Posted - 2010-03-10 : 23:05:10
|
I already tried the same way you mentioned below, but that won't work for my case. I guess I have to figure out the code for duedate of each quarter ...then calculate interest...however, I have not come up any idea yet....thanks,quote:
Originally posted by DP978
ntn how are you calculating interest? And what is your starting amount.I used 132.59 as the base amount.And then used...Select DateDiff(mm,'4/1/1996',GetDate()) = 167 months (I assumed this for 2nd qtr 1996)Then did 132.59 * ( 1.01 ^ (167)) to calculate interest and only got $698.52.I even added another % to assume all had late charges.132.59 * ( 1.02 ^ (167)) and got $3,680.02, which is also not your value.The closes I got was at 152 mo. overdue...So help me on this equation here :)Edit:Unless they got 167 monthly interest charges, and then 55 quarterly interest charges compounded on that.
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-03-10 : 23:28:59
|
try posting your table DDL and sample data in DML. Also show the expected result
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
how to calculate interest