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

    Need optimal solution for interval match

    Priyabrata Das

      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:

         

      UIDMachineLog-inLog-Out
      1AA12/3/2016 12:2013/3/2016 12:40
      2AA13/3/2016 15:0313/3/2016 15:33
      2AB14/3/2016 16:1114/3/2016 16:42
      3DC15/3/2016 14:0015/3/2016 15:00
      4BC16/3/2016 16:0016/3/2016 18:00
      5AA17/3/2016 12:2017/3/2016 13:20
      54AB18/3/2016 12:3418/3/2016 13:14
      3DC19/3/2016 12:0019/3/2016 13:05

       

      Access Time:

         

      UIDMachineResource Accessed time
      1AA12/3/2016 12:20
      2AA13/3/2016 15:03
      2AB14/3/2016 16:11
      3DC15/3/2016 14:00
      4BC16/3/2016 16:00
      5AA17/3/2016 12:20
      54z18/3/2016 12:34
      3z19/3/2016 12:00
      4z16/3/2016 12:00
      5Ay17/3/2016 22:20
      54Ay18/3/2016 22:34
      3xx19/3/2016 21:00