3 Replies Latest reply on Mar 16, 2016 9:22 AM by GreggSmith

    Xtraction Business Hours Filter?

    Rookie

      Would it be possible to filter Xtraction connected to CA Service Desk based on business hours? Example: 8-5 M-F

        • 1. Re: Xtraction Business Hours Filter?
          karenpeacock SupportEmployee

          Moved question to Xtraction

           

          Best wishes

          Karen

          • 2. Re: Xtraction Business Hours Filter?
            mvanwyk SupportEmployee

            Hi Phyghy,

             

            If you are using the Incidents data source, in your list of fields you can group on, there should be a field named Open Date (Hour) that you can also filter on. This field shows the hour the incident was raised in the time zone of your CA Service Desk database server. You could add another field to the data model called Open Date (Day) that returns the day the incident was opened. With these two fields using as filter conditions, you should be able to restrict incident records that were only opened during business hours. It is worth noting that CA Service Desk stores dates in GMT time zone as an epoch (seconds since 1st Jan 1970) so they can be difficult to work with.

             

            Here is an example expression you could use in the Open Date (Day) field to return the day of the week the incident was opened:


            DATENAME(DW,DATEADD(HOUR,DATEDIFF(HOUR,GETUTCDATE(),GETDATE()),DATEADD(SECOND,REQUEST.open_date,'1970-01-01')))


            Let me know if this helps you out.

             

            Thanks

            Mark

            • 3. Re: Xtraction Business Hours Filter?
              GreggSmith SupportEmployee

              Keep in mind that this logic is only accurate for date/time values that are on the same side of a daylight savings change.  If this calculation is applied to a date/time value that is 6 months old, it will be off by an hour (either an hour ahead or behind).  If the time change occurred last week, dates from 2 weeks ago will be off.