asked on

QUERY TABLE INTEREST

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.

ASKER CERTIFIED SOLUTION

plummet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

emi_sastra

ASKER

Hi John,

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

Thank you very much for your help.

plummet

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

emi_sastra

ASKER

Sure, thank you.

Anuradha Goli

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

Open in new window

Data as shown in image

And output is based on interest type.

emi_sastra

ASKER

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.

plummet

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

Cheers
John

emi_sastra

ASKER

Let me open a new question.

Thank you.

emi_sastra

ASKER

Done.