0 Replies Latest reply: Mar 7, 2017 3:28 PM by Andrew Moberg RSS

    Interval Match multiple keys

    Andrew Moberg

      Hello

       

      I am trying to script an interval match function which would show me a number of minutes that occur between a start and stop time.

      Here is some example data:

       

      CASES:

      LOAD * INLINE

      [

      Log,Serv,Date,In,Out, RoomMins, RoomID

      123,Ortho,2/1/2017, 7:20,12:45,325, 4

      345,Ortho,2/1/2017, 9:15,11:20,125, 3

      567,Ortho,2/1/2017, 12:15,16:18,243,3

      789,Uro,2/1/2017, 11:15,13:46,151,5

      ];

       

       

      BLOCK:

      LOAD * INLINE

      [

      Serv,Date,Start,Stop, NumRooms

      Ortho,2/1/2017,7:20,15:00,1

      Uro,2/2/2017,7:20,17:00,1

      ];

       

      I need to be able to show the number of minutes that occurred between the Start and Stop times for each service. Using the above data I would expect the following:

      On 2/1 Ortho has one Room available

      On 2/1 Ortho had 3 logs:

      Since Log 123 was the first to start and it is an Ortho case, all of the RoomMins would be considered In Block

      Log 345's RoomMins would be considered Out of Block since Ortho only had 1 block available which was being used by Log 123

      Log 567 would have 135 of its 243 minutes considered In Block as Log 123 finished shortly after Log 567 started and Ortho still has available time until 15:00 (15:00-12:45 = 135 minutes)

      The Uro case would be considered Out of Block as they did not have block on this day.

       

      Thanks in advance.