5 Replies Latest reply: Aug 5, 2014 9:53 AM by Syed Muzammil RSS

    Chart over time period's where startdtime < period and endtime > period

    Michael Fasch

      Given is a table with the following values:

       

      NameStarttimeEndtime
      A8:0010:00
      B7:009:00
      ...

       

      I want to show a barchart with 48 bars where every bar is a time period of an half hour (7:00, 7:30, 8:00, 8:30)

       

      Each Bar should count the count of entries of the given table where the starttime is before the period and the endtime is after the period. The resultset should be:

       

      PeriodNumber of entries
      7:001
      7:301
      8:002
      8:302
      9:002
      9:301
      10:001

       

      My thoughts were to create a virtual table for the The Period's and connect them to the Data, but I have no idea how to connect the both sets, cause there is no unique foreign key for that it's kind of a "between-join"

       

      Do you have any idea's to solve that?