Issue while calculating SLA in SQL

Question

I am using below function to calculate the elapsed time between 2 input timestamps. Only time spent during business hours should be calculated. Business hrs are Mon-Sat 8 am to 6 pm.

Function call syntax: select xxxxx('2018.09.28 19:02:28','2018-09-29 10:40:35') Function is giving output as 98 mins, the correct answer is 160 mins.

Function structure is:

Create FUNCTION xxxxx (@LeadAssignTime DATETIME, @LeadContactTime DATETIME)
RETURNS VARCHAR(9)
AS
BEGIN
    DECLARE @Temp BIGINT
    SET @Temp=0

    DECLARE @LeadAssignDay VARCHAR(9)
    SET @LeadAssignDay = CONVERT(VARCHAR(9),@LeadAssignTime, 112)

    DECLARE @LeadContactDay VARCHAR(9)
    SET @LeadContactDay = CONVERT(VARCHAR(9),@LeadContactTime, 112)

    DECLARE @StartTime VARCHAR(9)
    SET @StartTime = CONVERT(VARCHAR(9),@LeadAssignTime, 108)

    DECLARE @FinishTime VARCHAR(9)
    SET @FinishTime = CONVERT(VARCHAR(9),@LeadContactTime, 108)

    DECLARE @WorkStart VARCHAR(9)
    SET @WorkStart = '08:00:00'

    DECLARE @WorkFinish VARCHAR(9)
    SET @WorkFinish = '18:00:00'

    IF (@StartTime<@WorkStart)
    BEGIN
        SET @StartTime = @WorkStart
    END
    IF (@FinishTime>@WorkFinish)
    BEGIN
        SET @FinishTime=@WorkFinish
    END

DECLARE @CurrentDate VARCHAR(9)
    SET @CurrentDate = CONVERT(VARCHAR(9),@LeadAssignTime, 112)
    DECLARE @LastDate VARCHAR(9)
    SET @LastDate = CONVERT(VARCHAR(9),@LeadContactTime, 112)

WHILE(@CurrentDate<=@LastDate)
BEGIN       

        IF (DATEPART(dw, @CurrentDate)!=1 )
        BEGIN
              IF (@CurrentDate!=@LeadAssignDay) AND (@CurrentDate!=@LeadContactDay)
              BEGIN
                   SET @Temp = (@Temp + (8*60))

              END

              ELSE IF (@CurrentDate=@LeadAssignDay) AND (@CurrentDate!=@LeadContactDay)
              BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)

              END

              ELSE IF (@CurrentDate!=@LeadAssignDay) AND (@CurrentDate=@LeadContactDay)
              BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)

              END

              ELSE IF (@CurrentDate=@LeadAssignDay) AND (@CurrentDate=@LeadContactDay)
              BEGIN
                SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)

              END

             END

SET @CurrentDate = CONVERT(VARCHAR(9),DATEADD(day, 1, @CurrentDate),112)

END
        Return @TEMP

END

This is the same as work time between two dates which has been asked and answered. Your code looks similar but does not match. https://stackoverflow.com/questions/5274208/calculate-business-hours-between-two-dates — Brian, Oct 03 '18 at 18:08
I would caution you against using scalar functions like this. They are horribly inefficient and slow. Then adding a cursor inside it is a performance timebomb. What you really need is a calendar table. It allows to do this kind of thing set based and offers the flexibility of things like holidays, the random required Saturday or even partial days. — Sean Lange, Oct 03 '18 at 18:12

score 0 · Accepted Answer · answered Oct 03 '18 at 18:47

You're going around your loop twice, but in the first iteration, you fall into the second IF block and setting @temp to be -62

In the second iteration, you fall into the third IF block and calculate 160 for the difference between @WorkStart and @FinishTime, but then this is added to the value already in @Temp. 160-62 = 98.

You'll need your second IF block to check if 'start time' is before 'work finish' before executing that logic.

(@CurrentDate=@LeadAssignDay) AND (@CurrentDate!=@LeadContactDay)

needs to become

(@CurrentDate=@LeadAssignDay) AND (@CurrentDate!=@LeadContactDay) AND ( @StartTime < @WorkFinish)

I haven't done any vetting beyond the one use case. Make sure to do some thorough testing.

I will do more testing on this. – Min89 Oct 04 '18 at 20:59 — Min89, Oct 04 '18 at 20:59

Issue while calculating SLA in SQL

I am using below function to calculate the elapsed time between 2 input timestamps. Only time spent during business hours should be calculated. Business hrs are Mon-Sat 8 am to 6 pm.

1 Answers1