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

      Hi

       

      With the following two data sets
      Behavior

      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
      homejkl

      2018-08-01 18:00:00

       

      Incidents

      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?

       

      1.PNG
      2.PNG