Sql select statement in sql server problem

Question

5.00/5 (1 vote)

See more:

Hai Every One,

I have a table like

Columns are
1.Loantype
2.Rate OF Interest
3.Days
4.EffectiveDate

These are the rows for that table

CSS

1   12  30  2011-11-24 00:00:00.000
1   14  60  2011-11-25 00:00:00.000
3   14  60  2011-10-11 00:00:00.000
2   12  30  2011-11-24 00:00:00.000
3   11  120 2011-11-25 00:00:00.000
4   15  60  2011-11-26 00:00:00.000
2   14  60  2011-11-25 00:00:00.000
4   11  30  2011-11-26 00:00:00.000
4   12  60  2011-11-27 00:00:00.000
3   12  120 2011-11-22 00:00:00.000
4   14  90  2011-11-30 00:00:00.000
4   16  120 2011-11-30 00:00:00.000
2   14  60  2011-11-25 00:00:00.000
1   10  30  2012-01-01 00:00:00.000
1   15  30  2012-01-03 00:00:00.000
3   16  30  2012-01-01 00:00:00.000
2   10  30  2012-01-01 00:00:00.000
2   15  60  2012-01-04 00:00:00.000

i want to select latest rate of interest for every loan type
ex:
for loan type 1 has 2 rate of interest for 30 days i want latest one i.e. 10 on 2012-01-03
My result should like this

1	15	30	2012-01-03 00:00:00.000
     1	14	60	2011-11-25 00:00:00.000
     2	14	60	2011-11-25 00:00:00.000
     2	14	60	2011-11-25 00:00:00.000
     3	16	30	2012-01-01 00:00:00.000
     3	14	60	2011-11-12 00:00:00.000

Please help me write select state for above result set

Thanks In Advance

Posted 12-Jan-12 2:00am

ratnakargudipati

Add a Solution

3 solutions

Solution 2

SQL

SELECT LAST(Rate_Of_Interest) FROM TABLE WHERE Loantype = 1.

What will retrieve the lastest rate of interest value for a given loantype.

But if you want to know the lastest, in the meaning of the last EffectiveDate as I think you are trying to explain, you can simply do:

For a given loantype:

SQL

SELECT MAX(EffectiveDate)FROM TABLE WHERE Loantype = 1

For every one:

SQL

SELECT * FROM TABLE WHERE EffectiveDate = (SELECT MAX(DATE) FROM TABLE)

You will ned the effective date as column type date, so the string can be parsed (as date obviously)

You can also do a stored procedure so you can retrieve the present date (using getDate) and get you can find the difference using DATEDIFF.

More info http://www.sql-server-performance.com/2007/datetime-2008/[here]

Let me know if this helps you.

Posted 12-Jan-12 2:29am

Maxdd 7

Updated 12-Jan-12 2:32am

v3

Solution 1

May not be the best, but off the top of my head

SQL

SELECT [columns] FROM [table]
WHERE Date = (SELECT MAX(date) FROM table)

Posted 12-Jan-12 2:14am

Not Active

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

berghain · Accepted Answer · 2012-01-12T03:30:00

Solution 3

How about:

SQL

;with [cte] as 
(
select 
    Loantype, 
    Days,
    [Rate OF Interest], 
    rowNumber = row_number() over (partition by Loantype, Day order by EffectiveDate desc)
from [table]
)
select 
    Loantype,
    Days,
    [Rate OF Interest]
from [cte]
where rowNumber = 1

Posted 12-Jan-12 3:30am

berghain

Comments

Amir Mahfoozi 12-Jan-12 9:42am

+5 Exact answer.

[no name] 12-Jan-12 16:53pm

Just keep in mind Common Table Expressions are only available with SQL Server 2005 and above. The OP didn't specify what version was being used.

[no name] 12-Jan-12 23:59pm

thank You very much.
I got the result.
First time i learned the concept about CTE.
Once again thank you