Forum Discussion

javirmerino's avatar
javirmerino
Helper III
6 years ago

Calculating Required Completion Time/Date based on predetermined SLAs

Hi Guys, i'm hoping one of you geniuses (Genii?) will be able to help with an query i need to construct...   I've got a table full of ticket requests, each with a predetermined SLA, that i need to ...
Need Help
Tutorial Requests
javirmerino's avatar
javirmerino
Helper III
6 years ago

Hi Anonymous, thanks for your comment.

 

I've connected using a sql server connection into our applications server where the SysAid data is held.

 

Do you currently use SysAid, then? What's your opinions?


A

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi javirmerino ,

 

Sorry for delay in response, We can try to use the following calculated column to meet your requirement:

 

Required Completion Date =
IF (
    [Required Completion Date] = BLANK ()
        || [Required Completion Date] = "",
    VAR d = [Date]
    VAR t = [Time]
    VAR HourValue =
        SWITCH ( [Urgency], "Urgent", 4, "Very High", 8, "Normal", 24, "Low", 40 )
    VAR isWorkDay =
        CALCULATE (
            COUNTROWS ( 'Dim_Date' ),
            FILTER (
                'Dim_Date',
                [Date] = d
                    && [IsHolidayUK]
                    && ( WEEKDAY ( [Date] ) IN { 1, 7 } )
            )
        ) = 0
    VAR beforeWorkTime =
        t < TIME ( 9, 0, 0 )
    VAR inWorkTime =
        AND ( t >= TIME ( 9, 0, 0 ), t <= TIME ( 17, 0, 0 ) )
    RETURN
        SWITCH (
            TRUE (),
            isWorkDay && inWorkTime, d + t + HourValue / 24,
            isWorkDay && BeforeWorkTime, d + TIME ( 9, 0, 0 ) + HourValue / 24,
            CALCULATE (
                MIN ( 'Dim_Date'[Date] ),
                FILTER (
                    'Dim_Date',
                    [Date] > d
                        && NOT ( [IsHolidayUK] )
                        && NOT ( WEEKDAY ( [Date] ) IN { 1, 7 } )
                )
            )
                + TIME ( 9, 0, 0 ) + HourValue / 24
        ),
    [Required Completion Date]
)

 


Best regards,