QUERY TABLE INTEREST

emi_sastra
emi_sastra used Ask the Experts™
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
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

Open in new window


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

Hope it helps
John

Author

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

Author

Commented:
Sure, thank you.
Anuradha GoliSystems Development / Support Specialist

Commented:
Structure is defined in the image might help you in easy way.
Table structure
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

Open in new window

Data as shown in image sample dataAnd output is based on interest type.

Author

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

Author

Commented:
Let me open a new question.

Thank you.

Author

Commented:
Done.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial