I have written a SQL query that uses cursors and I realized that it eats up too many resources on the server and it is also slow.
So, this is the table:
I need to calculate the time difference between Status = Assigned and Status = In Progress where Assigned_group is or prev_assigned_group is like "%Hotline%" .
I also have a sequence so I select distinct the Incident id and order the rows ascending by Incident_Sequence because I have to start with the smallest sequence number.
So far so good.
I start with the first row and jump to the next one until I find that the Status is "In Progress".When status is found I calculate the difference between the first assigned row and the row where the status In progress was found.
So the big question is: Can I do this without cursors? If yes, then how ?
SET NOCOUNT ON
DECLARE @day DATE
SET @day = '20160606'
CREATE TABLE #result(
[Assigned_Group] NVARCHAR(100),
[ProgressTime] INTEGER,
[Tickets] INTEGER,
[Avarage] FLOAT
)
INSERT INTO #result(Assigned_Group,ProgressTime,Tickets,Avarage)
SELECT DISTINCT Assigned_Group,0,0,0.0
FROM [grs_dwh].[smt].[Aht]
WHERE (Assigned_Group like 'CI-Hotline%' OR Prev_assigned_Group like 'CI-Hotline%')
and CONVERT(DATE,Last_Modified_Date, 104) = @day
-- raw
SELECT [Incident_Sequence]
,[Incident_Id]
,[Assigned_Group]
,[Prev_assigned_Group]
,[Status]
,[Last_Modified_Date]
,[Service]
INTO #rawData
FROM [grs_dwh].[smt].[Aht]
WHERE (Assigned_Group like 'CI-Hotline%' OR Prev_assigned_Group like 'CI-Hotline%')
and CONVERT(DATE,Last_Modified_Date, 104) = @day
ORDER BY Incident_Sequence asc
--CREATE TABLE #orderList(
--)
SELECT DISTINCT[Incident_id] INTO #incidentList FROM #rawData
DECLARE cur0 CURSOR FOR SELECT incident_Id FROM #incidentList
DECLARE @currentIncident NVARCHAR(15)
OPEN cur0
FETCH next from cur0 INTO @currentIncident
WHILE @@FETCH_STATUS = 0
BEGIN
-- PRINT @currentIncident
SELECT * INTO #tmpTable FROM #rawData WHERE Incident_Id = @currentIncident ORDER BY Incident_Sequence
DECLARE cur1 CURSOR FOR SELECT * FROM #tmpTable ORDER BY Incident_Sequence ASC
DECLARE @incident_Sequence INTEGER
DECLARE @incident_Id NVARCHAR(50)
DECLARE @assigned_Group NVARCHAR(100)
DECLARE @previous_Assiggned NVARCHAR(100)
DECLARE @status NVARCHAR(50)
DECLARE @last_Modified_Date DATETIME
DECLARE @service NVARCHAR(50)
DECLARE @progressFound BIT
DECLARE @startProgressDate DATETIME
DECLARE @ticketProgressTime INTEGER
DECLARE @resultGroup NVARCHAR(100)
SET @progressFound = 0
OPEN cur1
FETCH next from cur1
INTO @incident_Sequence, @incident_Id, @assigned_Group, @previous_Assiggned, @status, @last_Modified_Date, @service
WHILE @@FETCH_STATUS = 0
BEGIN
IF @progressFound = 0 AND @status <> 'In Progress'
BEGIN
FETCH next from cur1 INTO @incident_Sequence, @incident_Id, @assigned_Group, @previous_Assiggned, @status, @last_Modified_Date, @service
CONTINUE
END
IF @progressFound = 0
BEGIN
SET @startProgressDate = @last_Modified_Date
SET @resultGroup = @assigned_Group
SET @progressFound = 1
FETCH next from cur1 INTO @incident_Sequence, @incident_Id, @assigned_Group, @previous_Assiggned, @status, @last_Modified_Date, @service
CONTINUE
END
ELSE
BEGIN
SET @ticketProgressTime = DATEDIFF(SECOND, @startProgressDate, @last_Modified_Date)
UPDATE #result SET ProgressTime = ProgressTime + @ticketProgressTime, Tickets = Tickets+1 WHERE Assigned_Group = @resultGroup
SET @ticketProgressTime = 0
SET @progressFound = 0
END
FETCH next from cur1
INTO @incident_Sequence, @incident_Id, @assigned_Group, @previous_Assiggned, @status, @last_Modified_Date, @service
END
CLOSE cur1
DEALLOCATE cur1
--IF @incident_Id = 'INC000010047798'
--SELECT * FROM #tmpTable ORDER BY Incident_Sequence ASC
DROP TABLE #tmpTable
FETCH next from cur0 INTO @currentIncident
END
CLOSE cur0
DEALLOCATE cur0
SET NUMERIC_ROUNDABORT OFF
UPDATE #result SET Avarage = CAST(ProgressTime AS float) / CASE WHEN Tickets = 0 THEN 1 ELSE CAST(Tickets AS float) END
SELECT * FROM #result
ORDER BY 1 asc
DROP TABLE #result
DROP TABLE #rawData
DROP TABLE #incidentList
