beginner SQL help - 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

beginner SQL help

Author

Topic

jdyahoo
Starting Member

1 Post

Posted - 2008-03-19 : 13:40:17

Hi all,

I've been banging my head against this issue, and I haven't managed to find a solution. I was hoping that maybe somebody here has done something similar.

I am trying to get the latest available pricing from a Rate table, which is based on the customer ID and the provided date from another table (Table1).

This is what the code looks like, essentially:

select
table1.*
,table2.*
,table3.Rate

from table1
left outer join table2 on table1.Id = table2.Id
left outer join (
Select Top 1 Rate from RateTable
Where RateTable.date < table1.date
order by RateTable.date desc
) as table3 on table3.custId = table1.custId

I understood that table1.date can't be explicitly passed into the nested join within table3, but does anybody know a work around that can achieve the above example (the environment is SQL server 2000 and inside a view not on in a stored proc)?

Thanks in advance for any tips or workaround.

modi_sanjay
Starting Member

9 Posts

Posted - 2008-03-19 : 15:24:15

Hi I think you forget to put table1 in inside the ionner part of query. i mark the table name and condition in red color

select table1.*,table2.*,table3.Rate
.from table1 left outer join table2 on table1.Id = table2.Id
left outer join (
Select Top 1 Rate
from RateTable,table1
Where RateTable.date < table1.date AND table1.colID = Ratetable.ColID
order by RateTable.date desc
) as table3 on table3.custId = table1.custId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources