Query 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query help

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-11-05 : 11:15:52

I have two tables and looks like

TblxA

VisitID, AdminDateTime,User
123,11/4/10 10:20:00, abc
234,11/4/10 8:20:00, def

TblxB
VisitID,EventDateTime,Location
123, 11/4/10 8:00:00, L1
123, 11/4/10 10:00:00, L2
123, 11/4/10 10:30:00, L3
234, 11/4/10 7:00:00,p1
234, 11/4/10 8:00:00,Null
234, 11/4/10 8:30:00,p3

i would like the query to return
A.VisitID,A.AdminDateTime,A.User,B.Location
with the logic being at the AdminDateTime what was the location comparing it with the EventDateTime of Table B
for example VisitID 123 with admindatetime being 10:20 the location should be L2 ( since there was no change from 10 to 10:30 for this visit)

please help me out. tryied a lot..

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-11-05 : 12:08:22

If you're on sql 2005

SELECT A.VisitID,A.AdminDateTime,A.aUser,C.Location
FROM tblXA A
CROSS APPLY
( select top 1 location
from tblXb b
where a.visitid = b.visitid
and a.AdminDateTime >= b.EventDateTime
order by b.visitid asc, eventdatetime desc
) c
Jim

Everyday I learn something that somebody else already knew

shebert
Yak Posting Veteran

85 Posts

Posted - 2010-11-05 : 12:10:27

how about this

select a.visitId, a.adminDateTime,A.user,b.location
from tablea a join tableb b on a.visitId=b.visitID
where a.adminDateTime<=b.eventDateTime

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources