9 Replies Latest reply on May 15, 2015 7:05 AM by kettan

    Sum time intervals on a date placed over multiple rows i data?

    Tine Skoett Christensen

      The table show time slots booked for a room.

      For some dates the room has been booked several times, like 12-16 and then 20-21:30.

       

      Booking times:

      08-01-2014 12:00 08-01-2014 16:00
      08-01-2014 19:00 08-01-2014 19:30
      09-01-2014 07:30 09-01-2014 15:30
      09-01-2014 20:00 09-01-2014 21:30
      10-01-2014 20:00 10-01-2014 21:30
      11-01-2014 17:00 11-01-2014 18:30
      13-01-2014 07:30 13-01-2014 22:30
      14-01-2014 12:00 14-01-2014 16:00
      15-01-2014 12:00 15-01-2014 16:00
      15-01-2014 20:00 15-01-2014 21:30
      16-01-2014 12:00 16-01-2014 16:00
      16-01-2014 20:00 16-01-2014 21:30
      17-01-2014 12:00 17-01-2014 16:00
      17-01-2014 20:00 17-01-2014 21:30

       

      These bookings are placed in different rows so when I want to calculate the number of hours NOT booked I get a wrong result.

       

      To calculate the number of hours booked I've used the following formula:

      (DATEDIFF('second',[FrameStartTime],[FrameEndTime]))/(60*60)(DATEDIFF('second',[FrameStartTime],[FrameEndTime]))/(60*60)

       

      It appears to calculate for each row, and not for each date. This is fine when I want to sum but how do I find the available time (NOT booked) on a given date?

       

      Thanks in advance.

       

      Best Regards,

      Tine