Sanky Sanky - 2 months ago 8
SQL Question

Calculate %SLA by considering business hours to fetch the right tickets

I have a code that helps me fetch tickets that have met SLA while considering only two factors out of the 5 on my list and i need to incorporate the same in my code that can help me fetch the right tickets


  1. time_to_accept the ticket <=10 minutes----> done

  2. time_to_resolve the ticket 'low' then 960 minutes, 'medium' then 480,'high' then 120, 'Urgent' then 60.-->done

  3. I need to calculate %SLA while considering all the below factors in addition to the above two factors like business hours which are
    between 4:30am to 10:30pm from mon-friday and 4:30am-5:30pm on
    saturday-saunday and bank holidays.


Answer

Ok, I'll bite. I will assume that TicketTime is datetime or timestamp

select Gubbins
from Table
where ( datepart(dw,TicketTime) between 2 and 6 -- Mon-Fri
and cast(TicketTime as time) between '04:30:00' and '22:30:00' )
or 
( (datepart(dw,TicketTime) in (1,7) -- Sat, Sun
  or cast(TicketTime as date) in (select Holidays from HolidayTable))
and cast(TicketTime as time) between '04:30:00' and '17:30:00' )
Comments