Many to One - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Many to One

Author

Topic

Les.61
Starting Member

49 Posts

Posted - 2009-11-29 : 23:08:51

I have atable that has several lines of code for each client as follows
Client Number Rate Date
123........... 7.5 ... 2009-06-06
123........... 8.5 ... 2009-01-01
123........... 8.75... 2008-10-10
158........... 7.5 ... 2009-06-06
158........... 8.5 ... 2009-01-01
etc

I am trying to extract from this table the most recent dated line for each client and join with a tables that have Primary Keys. So far my query is as follows:

use reporting
go
select c.surname, c.givennames, ta.clientid, ta.balance, ta.id
from dbo.Client as c
inner join dbo.TranAccount as ta
on c.clientid = ta.clientid
inner join (select top 1
RatesId, Date, Rate
from dbo.LRates) as lr
on lr.RatesId = ta.id
where ta.account like 'L%'
order by ta.id

The result is that I only get 1 result and not one for each client and also how do I make sure I am getting the most recent data. The data could have a date that is in advance and I would not want to use and 'post dated' dates.

Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 09:29:51

should be this


use reporting
go
select c.surname, c.givennames, ta.clientid, ta.balance, ta.id
from dbo.Client as c
inner join dbo.TranAccount as ta
on c.clientid = ta.clientid
inner join (select r1.RatesId, r1.Date, r1.Rate
from dbo.LRates r1
join (select RatesId,max(date) as latest
      from dbo.LRates
      group by ratesId)r2
on r2.RatesId=r1.RatesId
and r2.latest=r1.date) as lr
on lr.RatesId = ta.id
where ta.account like 'L%'
order by ta.id

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-01 : 04:55:48

Though be aware that dates are not guaranteed to be unique so, that join could still give you more than 1 result in some circumstances.

You can use ROW_NUMBER() with PARTITION to generate a sequence instead if you require it.

(assuming you are using sql server 2005 or later)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Les.61
Starting Member

49 Posts

Posted - 2009-12-07 : 20:51:37

Thanks. Worked a treat. The changes of two dates the same for each client is non existent as the rate is the interest rate being changed and if we were to have 2 changes on one day we actually alter the original rate rather then adding a new rate as the system we use will not allow us to have 2 rates on the same day.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources