2 Replies Latest reply: Nov 3, 2016 6:05 AM by Priyabrata Das

# Need optimal solution for interval match

Hi,

I have the below two tables.

Table Online Period : It has info about the PC and duration in which a user is online.

Table Access Time : It has the info about the Time and the Machine through which the user access a resource.

Relation : User ID, Machine and time interval

Use Case: The user may access the resource from any computers . Not restricted to the machines available in Table "Online Period".

Result Needed: I want to find who are the users have accessed the resources from the PCs available In the table "Online Period". and who are accessing from other PCs.

Condition : User is considered that, he accessed the resource from the Machines available in table "Online Period" is when UID and Machine are same and the Resource Accessed time is with in the Log-in and Log-out.

Online Period:

 UID Machine Log-in Log-Out 1 AA 12/3/2016 12:20 13/3/2016 12:40 2 AA 13/3/2016 15:03 13/3/2016 15:33 2 AB 14/3/2016 16:11 14/3/2016 16:42 3 DC 15/3/2016 14:00 15/3/2016 15:00 4 BC 16/3/2016 16:00 16/3/2016 18:00 5 AA 17/3/2016 12:20 17/3/2016 13:20 54 AB 18/3/2016 12:34 18/3/2016 13:14 3 DC 19/3/2016 12:00 19/3/2016 13:05

Access Time:

 UID Machine Resource Accessed time 1 AA 12/3/2016 12:20 2 AA 13/3/2016 15:03 2 AB 14/3/2016 16:11 3 DC 15/3/2016 14:00 4 BC 16/3/2016 16:00 5 AA 17/3/2016 12:20 54 z 18/3/2016 12:34 3 z 19/3/2016 12:00 4 z 16/3/2016 12:00 5 Ay 17/3/2016 22:20 54 Ay 18/3/2016 22:34 3 xx 19/3/2016 21:00
• ###### Re: Need optimal solution for interval match

What do you mean when you say optimal solution of interval match? Is there anything specific you are looking to get?

• ###### Re: Need optimal solution for interval match

Hi Sunny,

Since I have millions of rows, joining the tables after the interval match is very difficult.

I would like to have a flag in the "Access Time" table to say, it is accessed from the available list of PC from Table "Online Period" or Others.