Need Help: join tables - 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

Need Help: join tables

Author

Topic

JasonAnt
Starting Member

23 Posts

Posted - 2007-09-07 : 23:48:36

I have tbPrice:

Item, EffectiveDate, Price

and I have tbTransaction:

TransDate, Item

tbPrice created only if the price is changed, its not exist in everyday, If transdate not exist in tbPrice then find the nearest date

how to make query of this?
TransDate, Item, Price

Thank You

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-08 : 00:10:44

this ?


select t.TransDate, t.Item, 
       Price = (select top 1 x.Price from tbPrice x where x.Item = t.Item order by EffectiveDate desc)
from   tbTransaction t

KH
[spoiler]Time is always against us[/spoiler]

JasonAnt
Starting Member

23 Posts

Posted - 2007-09-08 : 00:24:06

Thank You KHTan,

select t.TransDate, t.Item,
Price = (select top 1 x.Price from tbPrice x
where x.Item = t.Item and x.EffectiveDate <= t.TransDate order by EffectiveDate desc)
from tbTransaction t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources