May 4, 2016 at 9:08 am
Hi all,
I need assistance with regard to a DBA task. I need to create an SLA report that will show incidents that were resolved after the SLA period which is 4 hours. I am using SQL Server 2014 with a Heat 2013 application. I have been attempting to execute the code below with no success, please assist.
The code attempts to subtract weekends, holidays, lunch and after hours so the technicians can be rated fairly, however I'm not sure what will happen when a technician takes leave (the system should not count such a day). When I compare the hours returned by the script and the hours I get when calculating manually, the difference is too big. I have a difference of about 13 hours which shows me something is really wrong.
This is the list of the main tables : [dbo].[Asgnmnt] , [dbo].[CallLog] , [dbo].Profile]
[CallLog] is where incidents/calls are initially registered
[Asgnmnt] keeps record of incidents/calls that have been assigned to technicians
[Profile] reads from the A.D, it stores employees of the whole organisation, Please find the script below:
SELECT a.callid [Call ID], a.AssignedBy [Assigned By], a.Assignee [Technician],
(p.FirstName + ' ' + p.LastName)[Client Name],
----OUT OF THE FULL WEEK SUBTRACT THE WEEKEND AND DETERMINE THE START TIME OF THE SYSTEM (8:00)
ISNULL((((DATEDIFF(dd,a.dtAssigned,a.dtResolved)-1) --Start with total number of days including weekends
- (DATEDIFF(wk,a.dtAssigned,a.dtResolved)*2) --Subtract 2 days for each full weekend
- (1-SIGN(DATEDIFF(dd,6,a.dtAssigned)%7)) --If StartDate is a Sunday, Subtract 1
- (1-SIGN(DATEDIFF(dd,5,a.dtResolved) %7))) * 465) --This will give us full days minus one that we'll complete with following operations
+ (SELECT CASE WHEN a.dtAssigned <= DATEADD(MI, 480, DATEADD(DD, DATEDIFF(DD, 0, a.dtAssigned), 0))
THEN 465 --if Start Date is earlier than 8:00 then it counts as full day
WHEN a.dtAssigned >= DATEADD(MI, 990, DATEADD(DD, DATEDIFF(DD, 0, a.dtAssigned), 0))
THEN 0 --if Start Date is later than 16:30 then it won't count
ELSE DATEDIFF(MI, a.dtAssigned, DATEADD(MI, 990, DATEADD(DD, DATEDIFF(DD, 0, a.dtAssigned), 0))) --Minutes between start date and 16:30
-
------------- SUBTRACT ACKNOWLEDGED TIME
CASE WHEN a.dtAssigned <= DATEADD(MI, 750, DATEADD(DD, DATEDIFF(DD, 0, a.dtAssigned), 0))
THEN 45 --If it's earlier than lunch, substract whole lunch time
WHEN a.dtAssigned <= DATEADD(MI, 795, DATEADD(DD, DATEDIFF(DD, 0, a.dtAssigned), 0))
--If it's earlier than the end of lunch time, substract corresponding minutes
THEN DATEDIFF( MI, a.dtAssigned, DATEADD(MI, 795, DATEADD(DD, DATEDIFF(DD, 0, a.dtAssigned), 0)))
ELSE 0
END
END
-----------DETERMINE THE RESOLVED TIME (16:30) AND CANNOT BE EARLIER THAT 8:00
+ CASE WHEN a.dtResolved <= DATEADD(MI, 480, DATEADD(DD, DATEDIFF(DD, 0, a.dtResolved), 0))
THEN 0 --if End Date is earlier than 8:00 then it won't count
WHEN a.dtResolved >= DATEADD(MI, 990, DATEADD(DD, DATEDIFF(DD, 0, a.dtResolved), 0))
THEN 465 --if End Date is later than 16:30 then it counts as full day
ELSE DATEDIFF(MI, DATEADD(MI, 990, DATEADD(DD, DATEDIFF(DD, 0, a.dtResolved), 0)), a.dtResolved) --Minutes between 7:30 and end date
-
CASE WHEN a.dtResolved >= DATEADD(MI, 795, DATEADD(DD, DATEDIFF(DD, 0, a.dtResolved), 0))
THEN 45 --If it's later than lunch, substract whole lunch time
WHEN a.dtResolved >= DATEADD(MI, 750, DATEADD(DD, DATEDIFF(DD, 0, a.dtResolved), 0))
--If it's later than the start of lunch time, substract corresponding minutes
THEN DATEDIFF( MI, DATEADD(MI, 750, DATEADD(DD, DATEDIFF(DD, 0, a.dtResolved), 0)),
a.dtResolved)
ELSE 0
END
END
FROM Asgnmnt a INNER JOIN CallLog c ON a.CallID = c.CallID
WHERE a.dtAssigned <= a.dtResolved )
------------SUBTRACT THE HOLIDAYS IF FOUND
- (( SELECT count(*)
FROM dbo.tblHoliday
WHERE dtHdate BETWEEN a.dtAssigned AND a.dtResolved) * 7.75)
------------ THE NEXT LINE IS CLOSING THE ISNULL AT THE TOP, CONVERTING MINS TO HRS
,0) / 60.00 AS [Hours Spent], datediff(mi, a.dtAssigned, a.dtAssigned) [Accepted After (min)]
FROM Asgnmnt a INNER JOIN CallLog c ON a.CallID = c.CallID
LEFT JOIN [Profile] p ON c.CustID = p.CustID
WHERE a.dtAssigned between '2015-03-01' and '2015-03-31'
AND (a.dtAssigned <= a.dtResolved )
-------------AND THIS IS ALSO A BIG CHALLENGE FOR ME
-------------[Hours Spent] is Column number 5 in my select statement, From ISNULL until the Holiday
AND [Hours Spent] > 4
Please assist guys.............
May 4, 2016 at 11:25 am
The code attempts to subtract weekends, holidays...
I would do that part with a Calendar table... then you need pretty much ZERO logic for it.
WHERE Calendar.IsWeekend = False AND Calendar.IsHoliday=False...
May 5, 2016 at 6:39 am
Thanks for that piece Pietlinden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply