# QUERY TABLE INTEREST

on
Hi All,

I have 2 tables :

TableDeposit :

1. AccountNo
2. InterestType
3. DepositAmount

TableInterest :

1. InterestType
2. DepositAmount1
3. InterestRate1
4. DepositAmount2
5. InterestRate2
6. DepositAmount3
7. InterestRate3
8. DepositAmount4
9. InterestRate4
10. InterestRate5

The DepositAmount at TableInterest is the limit of Deposit Amount for a certain Interest Rate.

How do I get the right Interest Rate from TableInterest by joining those tables ?

Thank you.
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I think you need to do something like this:

``````select
dep.*,
case
when dep.DepositAmount <= int.DepositAmount1 then InterestRate1
when dep.DepositAmount <= int.DepositAmount2 then InterestRate2
when dep.DepositAmount <= int.DepositAmount3 then InterestRate3
when dep.DepositAmount <= int.DepositAmount4 then InterestRate4
else InterestRate5
end as InterestRate

from TableDeposit dep
left join TableInterest int
on dep.InterestType = int.InterestType
``````

I have assumed the deposit amount is less than or equal to the amount in TableInterest.

Hope it helps
John

Commented:
Hi John,

I've not tried it, but I think it should work.

Thank you very much for your help.

Commented:
No problem - let me know if it doesn't work as you want

Commented:
Sure, thank you.
Systems Development / Support Specialist

Commented:
Structure is defined in the image might help you in easy way.
``````DECLARE		@INTERESTTYPE CHAR(10)
SET			@INTERESTTYPE = 'INTY2';

WITH	LASTVISIT ( DEPOSITAMOUNT, INTERESTRATE )
AS
(
SELECT	DEPOSITAMOUNT,
INTERESTRATE
FROM	TABLEINTEREST
WHERE	INTERESTTYPE = @INTERESTTYPE
)
SELECT	TOP 1
LV.INTERESTRATE
FROM	TABLEDEPOSIT TD
INNER
JOIN	LASTVISIT LV
ON		TD.DEPOSITAMOUNT < LV.DEPOSITAMOUNT
ORDER
BY		LV.DEPOSITAMOUNT;
GO
``````
Data as shown in image And output is based on interest type.

Commented:
That's great.

I know the interest table is not normalized.
Since the structure is like that in dbf and I want to transfer to sql with the structure you suggest.

How could I do it ?

Should I open a new question for this ?

Thank you.

Commented:
I  think so, you should really try to normalise the table as anuradhay suggests but that is really a different question.

Cheers
John

Commented:
Let me open a new question.

Thank you.

Commented:
Done.

Do more with