Friday, July 2, 2010

What's at a location at a given time?

Today I was challenged with a problem that seemed deceptively simple and the answer is. But coming up with it took a while. The question was for a location log, what were all the objects at a given location at a given time?

The table is simple:
LocationID int
ObjectID int
Date datetime

So, {1,1,6/30/10 10am} would indicate that object 1 was at location 1 at 10am on 6/30/10.

Given a set of log entries:

{1,1,6/30/10 10am}
{2,1,6/30/10 12pm}
{4,1,6/30/10 6pm}
{2,1,6/30/10 8pm}
{1,2,6/30/10 11am}
{3,2,6/30/10 1pm}
{4,2,6/30/10 5pm}

We can see from this list that at 11am for location 1, both object 1 & 2 are there. But at 12:01pm, only object 2 is left. So, how do we get this list for any given time?

Here you go..

declare @myDate datetime
set @myDate='2010-06-30 11:00'
declare @MyLocation int
set @MyLocation =2

select * from
LocationLog t1
left join LocationLog t2 on t1.ObjectID=t2.ObjectID and t2.Date > t1.Date and t2.Date <= @myDate
WHERE
t1.Date<=@MyDate
and t1.LocationID=@MyLocation
and t2.ObjectID is null