2 Replies Latest reply on Aug 24, 2018 7:07 AM by Shinichiro Murakami

    Filtering on a time interval within a max datetime

    Martin Johannesson



      With the following two data sets

      ActionboUserIdtimeOfOccurence (datetime)
      homeabc2018-08-01 19:55:00
      contact usabc2018-08-01 20:05:00
      app1abc2018-08-01 20:10:00
      homezyx2018-08-01 20:05:00

      2018-08-01 18:00:00



      NumberStateOpened (datetime)Resolved (datetime)Closed (datetime)
      1CLOSED2018-07-01 10:00:002018-07-01 11:00:002018-07-01 11:05:00
      2CLOSED2018-08-01 20:20:002018-08-01 23:00:002018-08-01 23:05:00



      I have been trying to get a list of unique boUserIds from the Behavior table that were 'impacted' by the lastest incident. By finding any action that was done in the interval of 20 minutes leading up the lastest opened time of an incident and tagging . Then afterwards filtering.



      [Behavior].[timeOfOccurence] < MAX([Incidents].[Opened]) AND [Behavior].[timeOfOccurence] < DATEADD('minutes', -20, MAX([Incidents].[Opened]))


      Expected outcome:
      abc - true
      zyx  - true

      jkl   - false


      But I am running into the issue of mixing aggregated function.
      Does anyone know the best way to get around this?