sql server - SQL Query: Search with list of tuples -

sql server - SQL Query: Search with list of tuples -

i have next table (simplified version) in sqlserver.

table events ----------------------------------------------------------- | room | user | entered | exited | ----------------------------------------------------------- | | jim | 2014-10-10t09:00:00 | 2014-10-10t09:10:00 | | b | jim | 2014-10-10t09:11:00 | 2014-10-10t09:22:30 | | | jill | 2014-10-10t09:00:00 | null | | c | jack | 2014-10-10t09:45:00 | 2014-10-10t10:00:00 | | | jack | 2014-10-10t10:01:00 | null | . . .

i need create query returns person's whereabouts in given timestamps. example: (jim @ 2014-10-09t09:05:00), (jim @ 2014-10-10t09:01:00), (jill @ 2014-10-10t09:10:00), ...

the result set must contain given user , timestamp found room (if any).

------------------------------------------ | user | timestamp | wasinroom | ------------------------------------------ | jim | 2014-10-09t09:05:00 | null | | jim | 2014-10-09t09:01:00 | | | jim | 2014-10-10t09:10:00 | |

the number of user-timestamp tuples can > 10 000.

the current implementation retrieves records events table , search in java code. hoping force logic sql. how?

i using mybatis framework create sql queries tuples can inlined query.

the basic query is:

select e.* events e e.user = 'jim' , '2014-10-09t09:05:00' >= e.entered , ('2014-10-09t09:05:00' <= e.exited or e.exited null) or e.user = 'jill' , '2014-10-10t09:10:00 >= e.entered , ('2014-10-10t09:10:00' <= e.exited or e.exited null) or . . .;

sql server can handle ridiculously big queries, can go on in vein. however, if have name/time values in table (or result of query), utilize join:

select ut.*, t.* usertimes ut left bring together events e on e.user = ut.user , ut.thetime >= et.entered , (ut.thetime <= exited or ut.exited null);

note utilize of left join here. ensures original rows in result set, when there no matches.

sql sql-server