1 Reply Latest reply on Jul 5, 2019 8:08 AM by Conor McGinn

    Analytics - how might I filter on a time period from 3am to 6am for all tickets submitted last month

    Conor McGinn
      Share This:

      Hi,

       

      In Analytics, I can modify the generated SQL code from say "dbo.fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Scheduled_Start_Date) <= ANA_RPT_OBJECTS_VW.Last_48Hours" statement to dbo.fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Scheduled_Start_Date) <= dateadd(hh,32,getdate())". The '32' refers to the next 32 hours from 4pm (4pm is the time the report is scheduled), so we want CRQs up to midnight the next day. Such modification returns the exact records required.

       

      However, if I select all records for last month for example, how might I extract just those tickets that were scheduled between say 3pm to 11pm (or to midnight at 00 hours). Does anyone know of an SQL statement that I might use to search on a time period with the month date period.

       

      Many thanks, Conor

        • 1. Re: Analytics - how might I filter on a time period from 3am to 6am for all tickets submitted last month
          Conor McGinn

          Hi All,

           

          After resolving a similar challenge in Smart Reporting, I have returned to his query with the same solution. Rather than trying to modify the query filter for a time period selection which does not seem to be possible, I found a formula in design mode to use as a filter.

           

          Firstly, create a variable with a formula to extract the hour from Scheduled Start Date Time.

           

           

          Secondly, create a Filter using that variable to select the hour range between 3 and 6, whch represents the period 3am to 6am.

           

           

          The records returned then are only those valid for that time period.

           

          Best regards, Conor

          1 of 1 people found this helpful