In my organisation, and no doubt many others, the problem stems from the fact that the developers writing SQL are not SQL developers. Rather they are [insert relevant coding language] developers. And in many programming languages processing is best performed on a row-by-row basis. Not so SQL, especially T-SQL.
There are quite a few blog posts out there discussing the virtues of set-based SQL and how to change cursors into set based queries. Here are a few:
SQL trigger - convert cursor with set based operation
Increase your SQL Server performance by replacing cursors with set operations
Alternatives To SQL Server Cursors (A good MSDN article with many problems/solution examples)
I thought I'd weigh in with a slightly more complicated stored procedure that greatly benefits from being converted to a set-based format. This is a real-world example from one of the applications I support. Some of the names have been changed to protect the innocent. In this post I will just layout the code. In the next post I will show some of the reasoning (and proof) behind why the set-based stored procedure is better.
Download scripts here: Original with cursor; Cursor removed
Read part 2
Here is the original:
CREATE PROCEDURE dbo.spWithCursor
(
@RefreshNumber integer
)
AS
DECLARE @Groups TABLE
(
GroupGUID uniqueidentifier,
WorkspaceGUID uniqueidentifier,
GroupLevel integer,
RefreshedAlready integer
)
DECLARE @Windows TABLE
(
WindowGUID uniqueidentifier,
WindowTypeGUID uniqueidentifier,
WorkspaceGUID uniqueidentifier,
TaskLastActioned datetime,
RefreshNumber integer
)
DECLARE @Results TABLE
(
WindowGUID uniqueidentifier,
WindowTypeGUID uniqueidentifier,
WorkspaceGUID uniqueidentifier,
NumberOfTasks integer default 0,
NumberOfDueTasks integer default 0,
SLAFailedTasks integer default 0,
TasksDueToday integer default 0,
DueTasksTimeOutstanding integer default 0,
SLAFailedTasksTimeOutstanding integer default 0,
DueTodayTimeOutstanding integer default 0,
AllTasksTimeOutstanding integer default 0,
DueTasksAverageAge bigint default 0,
DueTasksOldest datetime default NULL,
TaskLastActioned datetime default NULL,
AverageTasksPerHour integer default 0,
AverageTaskTimePerHour integer default 0,
WorkingHoursToClear float default 0,
LastRefresh datetime
)
DECLARE @Aggregates TABLE
(
AggWindowGUID uniqueidentifier,
AggWindowTypeGUID uniqueidentifier,
AggWorkspaceGUID uniqueidentifier,
NumberOfTasks integer default 0,
NumberOfDueTasks integer default 0,
SLAFailedTasks integer default 0,
TasksDueToday integer default 0,
DueTasksTimeOutstanding integer default 0,
SLAFailedTasksTimeOutstanding integer default 0,
DueTodayTimeOutstanding integer default 0,
AllTasksTimeOutstanding integer default 0,
DueTasksAverageAge bigint default 0,
DueTasksOldest datetime default NULL,
TaskLastActioned datetime default NULL,
AverageTasksPerHour integer default 0,
AverageTaskTimePerHour integer default 0,
WorkingHoursToClear float default 0,
LastRefresh datetime
)
DECLARE @ViewDocGUID uniqueidentifier
SELECT @ViewDocGUID = '{17BFE026-A378-44CA-A456-C2A827D8BDBD}' --View A Doc
DECLARE @ViewTelNoteGUID uniqueidentifier
SELECT @ViewTelNoteGUID = '{8AAA69BF-5B00-4AD4-9867-FE3A914CFAEE}' --View A Telephone Note
DECLARE @WindowGroupTypeGUID uniqueidentifier
SELECT @WindowGroupTypeGUID = '{98783573-775A-4607-AFB8-418860F49C27}'
SET NOCOUNT ON
INSERT INTO @Groups
SELECT LV.GroupGUID, WFWG.WorkspaceGUID, 1, 0
FROM Table_1 LV
JOIN Table_2 WFWG
ON WFWG.WindowGroupGUID = LV.GroupGUID
WHERE EndDate IS NULL
DECLARE @GroupGUID uniqueidentifier
DECLARE @WorkspaceGUID uniqueidentifier
DECLARE @GroupLevel integer
DECLARE @LoopLevel integer
DECLARE DataCursor CURSOR FOR (SELECT GroupGUID, WorkspaceGUID, GroupLevel
FROM @Groups)
OPEN DataCursor
FETCH NEXT FROM DataCursor INTO @GroupGUID, @WorkspaceGUID, @GroupLevel
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @Groups
SELECT WindoworGroupGUID, WFWG.WorkspaceGUID, @GroupLevel + 1, 0
FROM Table_3 LMM
JOIN Table_2 WFWG
ON WFWG.WindowGroupGUID = LMM.GroupGUID
WHERE GroupGUID = @GroupGUID
AND WindoworGroupTypeGUID = @WindowGroupTypeGUID
AND EndDate IS NULL
INSERT INTO @Windows
SELECT WindoworGroupGUID, WindoworGroupTypeGUID, @WorkspaceGUID, LastActioned, RefreshNumber
FROM Table_3
JOIN Table_4 WFW
ON WFW.WorkFloWindowGUID = WindoworGroupGUID
LEFT JOIN Table_5 AWFW2
ON AWFW2.WorkFloWindowGUID = WindoworGroupGUID
AND AWFW2.Flags & 1 = 1
LEFT JOIN Table_6 UI
ON UI.AccountGUID = AWFW2.AccountGUID
WHERE GroupGUID = @GroupGUID
AND WindoworGroupTypeGUID <> @WindowGroupTypeGUID
AND EndDate IS NULL
AND WindoworGroupGUID NOT IN (SELECT WindowGUID FROM @Windows)
FETCH NEXT FROM DataCursor INTO @GroupGUID, @WorkspaceGUID, @GroupLevel
END
CLOSE DataCursor
DEALLOCATE DataCursor
INSERT INTO @Windows
SELECT WE.WindowGUID, WFW.WindowTypeGUID, WFW.WorkspaceGUID, WFW.LastActioned, WFW.RefreshNumber
FROM Table_9 WE
JOIN Table_4 WFW
ON WFW.WorkFloWindowGUID = WE.WindowGUID
JOIN Table_7 EH
ON EH.EventGUID = WE.WindowEventGUID
LEFT JOIN @Windows W
ON W.WindowGUID = WE.WindowGUID
AND W.WorkspaceGUID = WFW.WorkspaceGUID
WHERE WFW.WorkspaceGUID IS NOT NULL
AND W.WindowGUID IS NULL
INSERT INTO @Windows
SELECT DISTINCT WE.WindowGUID, WFW.WindowTypeGUID, LTD.ChildGUID, WFW.LastActioned, WFW.RefreshNumber
FROM Table_8 LTD
JOIN Table_9 WE
ON WE.WindowEventGUID IS NOT NULL
JOIN Table_4 WFW
ON WFW.WorkFloWindowGUID = WE.WindowGUID
JOIN Table_7 EH
ON EH.EventGUID = WE.WindowEventGUID
LEFT JOIN @Windows W
ON W.WindowGUID = WE.WindowGUID
AND W.WorkspaceGUID = LTD.ChildGUID
WHERE WFW.WorkspaceGUID IS NULL
AND LTD.ParentGUID = '{43B03D8B-1333-4BF3-BEB6-A1446698223C}'
AND W.WindowGUID IS NULL
--Start Population of Data
INSERT INTO @Results
SELECT DISTINCT W.WindowGUID,
W.WindowTypeGUID,
W.WorkspaceGUID,
COUNT(1),
SUM(CASE WHEN DueDate <= GetDate() THEN 1 ELSE 0 END),
SUM(CASE WHEN SLADate <= GetDate() THEN 1 ELSE 0 END),
SUM(CASE WHEN DATEDIFF(day, DueDate, GetDate()) = 0 THEN 1 ELSE 0 END),
SUM(CASE WHEN DueDate <= GetDate() THEN EstimatedTaskCompletion ELSE 0 END),
SUM(CASE WHEN SLADate <= GetDate() THEN EstimatedTaskCompletion ELSE 0 END),
SUM(CASE WHEN DATEDIFF(day, DueDate, GetDate()) = 0 THEN EstimatedTaskCompletion ELSE 0 END),
SUM(EstimatedTaskCompletion),
AVG(CASE WHEN DueDate <= GetDate() THEN DateDiff(mi, DueDate , GetDate()) ELSE 0 END) * 60,
MIN(CASE WHEN DueDate <= GetDate() THEN DueDate ELSE NULL END),
MAX(W.TaskLastActioned),
dbo.fnGetAverageTasksActioned(W.WindowGUID, W.WorkspaceGUID, GetDate()),
dbo.fnGetAverageTaskTimeActioned(W.WindowGUID, W.WorkspaceGUID, GetDate()),
0, --Filled in below,
GetDate()
FROM @Windows W
JOIN Table_10 L
ON W.WindowGUID = L.WorkFloWindowGUID
JOIN Table_11 T
ON L.TaskGUID = T.TaskGUID
AND T.ActionDate IS NULL
AND T.WorkspaceGUID = W.WorkspaceGUID
JOIN Table_12 TTC
ON TTC.TaskTemplateGUID = T.TaskTemplateGUID
JOIN Table_13 TC
ON TC.TemplateClassGUID = TTC.TemplateClassGUID
AND TC.TemplateClassType = 1
JOIN Table_4 WFW
ON WFW.WorkFloWindowGUID = W.WindowGUID
AND ((WFW.WorkspaceGUID = W.WorkspaceGUID)
OR (WFW.WorkspaceGUID IS NULL))
LEFT JOIN Table_14 TA
ON TA.TaskGUID = T.TaskGUID
AND TA.ActionTypeGUID = @ViewDocGUID
LEFT JOIN Table_15 N
ON N.DocumentGUID = TA.Data
WHERE ((TA.ActionTypeGUID IS NULL) OR (N.DocumentGUID IS NOT NULL))
AND W.RefreshNumber = @RefreshNumber
GROUP BY W.WindowGUID, W.WindowTypeGUID, W.WorkspaceGUID, T.WorkspaceGUID
--Insert all data that isnt being refreshed
INSERT INTO @Results(R.WindowGUID,
R.WindowTypeGUID,
R.WorkspaceGUID,
R.NumberOfTasks,
R.NumberOfDueTasks,
R.SLAFailedTasks,
R.TasksDueToday,
R.DueTasksTimeOutstanding,
R.SLAFailedTasksTimeOutstanding,
R.DueTodayTimeOutstanding,
R.AllTasksTimeOutstanding,
R.DueTasksAverageAge,
R.DueTasksOldest,
R.TaskLastActioned,
R.AverageTasksPerHour,
R.AverageTaskTimePerHour,
R.WorkingHoursToClear,
R.LastRefresh)
SELECT DISTINCT [RowGUID], [RowTypeGUID], W.[WorkspaceGUID], [NumberOfTasks], [NumberOfDueTasks], [SLAFailedTasks], [TasksDueToday], [DueTasksTimeOutstanding], [SLAFailedTasksTimeOutstanding], [DueTodayTimeOutstanding], [AllTasksTimeOutstanding], [DueTask
sAverageAge], [DueTasksOldest], WFWS.[TaskLastActioned], [AverageTasksPerHour], [AverageTaskTimePerHour], [WorkingHoursToClear], [LastRefreshed]
FROM Table_16 WFWS
JOIN @Windows W
ON WFWS.RowGUID = W.WindowGUID
AND WFWS.WorkspaceGUID = W.WorkspaceGUID
WHERE W.RefreshNumber <> @RefreshNumber
--Insert any rows that have not been returned in the result set
--Just in case one of the windows now has no tasks. could left join the query but was causing bad query plan
INSERT INTO @Results(R.WindowGUID,
R.WindowTypeGUID,
R.WorkspaceGUID,
TaskLastActioned,
AverageTasksPerHour,
AverageTaskTimePerHour,
LastRefresh)
SELECT DISTINCT W.WindowGUID, W.WindowTypeGUID, W.WorkspaceGUID, W.TaskLastActioned, dbo.fnGetAverageTasksActioned(W.WindowGUID, W.WorkspaceGUID, GetDate()), dbo.fnGetAverageTaskTimeActioned(W.WindowGUID, W.WorkspaceGUID, GetDate()), GetDate()
FROM @Windows W
LEFT JOIN @Results R
ON R.WindowGUID = W.WindowGUID
AND R.WorkspaceGUID = W.WorkspaceGUID
WHERE R.WindowGUID IS NULL
--Calculate the Working Hours to Clear
--Need to insert into an Aggregate table so we can correctly calculate the Working hours values
INSERT INTO @Aggregates
SELECT *
FROM @Results
UPDATE @Results
SET WorkingHoursToClear = (SELECT CASE WHEN AverageTaskTimePerHour = 0 THEN NULL ELSE ((CAST(AllTasksTimeOutstanding as decimal(10,2)) / CAST(AverageTaskTimePerHour as decimal(10,2)) *60)*60) END
FROM @Aggregates R
WHERE WindowGUID = AggWindowGUID
And WorkspaceGUID = AggWorkspaceGUID)
--Do Group Calculations
DECLARE @RefreshedAlready integer
DECLARE GroupCursor CURSOR FOR (SELECT GroupGUID, GroupLevel
FROM @Groups) ORDER BY GroupLevel DESC
OPEN GroupCursor
FETCH NEXT FROM GroupCursor INTO @GroupGUID, @GroupLevel
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @RefreshedAlready = MAX(RefreshedAlready)
FROM @Groups
WHERE GroupGUID = @GroupGUID
IF @RefreshedAlready = 0
BEGIN
INSERT INTO @Results
SELECT WGM.GroupGUID,
@WindowGroupTypeGUID,
R.WorkspaceGUID,
SUM(NumberOfTasks),
SUM(NumberOfDueTasks),
SUM(SLAFailedTasks),
SUM(TasksDueToday),
SUM(DueTasksTimeOutstanding),
SUM(SLAFailedTasksTimeOutstanding),
SUM(DueTodayTimeOutstanding),
SUM(AllTasksTimeOutstanding),
AVG(DueTasksAverageAge),
MIN(DueTasksOldest),
MAX(TaskLastActioned),
AVG(AverageTasksPerHour),
AVG(AverageTaskTimePerHour),
SUM(WorkingHoursToClear),
MAX(LastRefresh)
FROM Table_3 WGM
JOIN @Results R
ON WGM.WindoworGroupGUID = R.WindowGUID
WHERE WGM.GroupGUID = @GroupGUID
AND WGM.EndDate IS NULL
GROUP BY WGM.GroupGUID , R.WorkspaceGUID
UPDATE @Groups
SET RefreshedAlready = 1
WHERE GroupGUID = @GroupGUID
END
FETCH NEXT FROM GroupCursor INTO @GroupGUID, @GroupLevel
END
CLOSE GroupCursor
DEALLOCATE GroupCursor
UPDATE WFWS
SET [NumberOfTasks] = R.NumberOfTasks,
[NumberOfDueTasks] = R.NumberOfDueTasks,
[SLAFailedTasks] = R.SLAFailedTasks,
[TasksDueToday] = R.TasksDueToday,
[DueTasksTimeOutstanding] = R.DueTasksTimeOutstanding,
[SLAFailedTasksTimeOutstanding] = R.SLAFailedTasksTimeOutstanding,
[DueTodayTimeOutstanding] = R.DueTodayTimeOutstanding,
[AllTasksTimeOutstanding] = R.AllTasksTimeOutstanding,
[DueTasksAverageAge] = R.DueTasksAverageAge,
[DueTasksOldest] = R.DueTasksOldest,
[TaskLastActioned] = R.TaskLastActioned,
[AverageTasksPerHour] = R.AverageTasksPerHour,
[AverageTaskTimePerHour] = R.AverageTaskTimePerHour,
[WorkingHoursToClear] = CAST(R.WorkingHoursToClear as Integer),
[LastRefreshed] = R.LastRefresh
FROM @Results R
JOIN Table_16 WFWS
ON R.WindowGUID = WFWS.RowGUID
AND R.WorkspaceGUID = WFWS.WorkspaceGUID
INSERT INTO Table_16([UniqueGUID], [RowGUID], [RowTypeGUID], [WorkspaceGUID], [NumberOfTasks], [NumberOfDueTasks], [SLAFailedTasks], [TasksDueToday], [DueTasksTimeOutstanding], [SLAFailedTasksTimeOutstanding], [DueTodayTimeOutstanding]
, [AllTasksTimeOutstanding], [DueTasksAverageAge], [DueTasksOldest], [TaskLastActioned], [AverageTasksPerHour], [AverageTaskTimePerHour], [WorkingHoursToClear], [LastRefreshed])
SELECT NewID(),
R.WindowGUID,
R.WindowTypeGUID,
R.WorkspaceGUID,
R.NumberOfTasks,
R.NumberOfDueTasks,
R.SLAFailedTasks,
R.TasksDueToday,
R.DueTasksTimeOutstanding,
R.SLAFailedTasksTimeOutstanding,
R.DueTodayTimeOutstanding,
R.AllTasksTimeOutstanding,
R.DueTasksAverageAge,
R.DueTasksOldest,
R.TaskLastActioned,
R.AverageTasksPerHour,
R.AverageTaskTimePerHour,
R.WorkingHoursToClear,
R.LastRefresh
FROM @Results R
LEFT JOIN Table_16 WFWS
ON R.WindowGUID = WFWS.RowGUID
AND R.WorkspaceGUID = WFWS.WorkspaceGUID
WHERE WFWS.RowGUID IS NULL
SELECT DISTINCT *
FROM @Results
SET NOCOUNT OFF
GO
And here is the modified version:
CREATE PROCEDURE dbo.spSetBasedDownload scripts here: Original with cursor; Cursor removed
(
@RefreshNumber integer
)
AS
DECLARE @Groups TABLE
(
GroupGUID uniqueidentifier primary key clustered,
WorkspaceGUID uniqueidentifier,
GroupLevel integer,
RefreshedAlready integer
)
DECLARE @Windows TABLE
(
WindowGUID uniqueidentifier primary key clustered,
WindowTypeGUID uniqueidentifier,
WorkspaceGUID uniqueidentifier,
TaskLastActioned datetime,
RefreshNumber integer
)
DECLARE @Results TABLE
(
WindowGUID uniqueidentifier primary key clustered,
WindowTypeGUID uniqueidentifier,
WorkspaceGUID uniqueidentifier,
NumberOfTasks integer default 0,
NumberOfDueTasks integer default 0,
SLAFailedTasks integer default 0,
TasksDueToday integer default 0,
DueTasksTimeOutstanding integer default 0,
SLAFailedTasksTimeOutstanding integer default 0,
DueTodayTimeOutstanding integer default 0,
AllTasksTimeOutstanding integer default 0,
DueTasksAverageAge bigint default 0,
DueTasksOldest datetime default NULL,
TaskLastActioned datetime default NULL,
AverageTasksPerHour integer default 0,
AverageTaskTimePerHour integer default 0,
WorkingHoursToClear float default 0,
LastRefresh datetime
)
DECLARE @Aggregates TABLE
(
AggWindowGUID uniqueidentifier primary key clustered,
AggWindowTypeGUID uniqueidentifier,
AggWorkspaceGUID uniqueidentifier,
NumberOfTasks integer default 0,
NumberOfDueTasks integer default 0,
SLAFailedTasks integer default 0,
TasksDueToday integer default 0,
DueTasksTimeOutstanding integer default 0,
SLAFailedTasksTimeOutstanding integer default 0,
DueTodayTimeOutstanding integer default 0,
AllTasksTimeOutstanding integer default 0,
DueTasksAverageAge bigint default 0,
DueTasksOldest datetime default NULL,
TaskLastActioned datetime default NULL,
AverageTasksPerHour integer default 0,
AverageTaskTimePerHour integer default 0,
WorkingHoursToClear float default 0,
LastRefresh datetime
)
DECLARE @ViewDocGUID uniqueidentifier
SELECT @ViewDocGUID = '{17BFE026-A378-44CA-A456-C2A827D8BDBD}' --View A Doc
DECLARE @ViewTelNoteGUID uniqueidentifier
SELECT @ViewTelNoteGUID = '{8AAA69BF-5B00-4AD4-9867-FE3A914CFAEE}' --View A Telephone Note
DECLARE @WindowGroupTypeGUID uniqueidentifier
SELECT @WindowGroupTypeGUID = '{98783573-775A-4607-AFB8-418860F49C27}'
SET NOCOUNT ON
INSERT INTO @Groups
SELECT LV.GroupGUID, WFWG.WorkspaceGUID, 1, 0
FROM Table_1 LV
JOIN Table_2 WFWG
ON WFWG.WindowGroupGUID = LV.GroupGUID
WHERE EndDate IS NULL
DECLARE @GroupGUID uniqueidentifier
DECLARE @WorkspaceGUID uniqueidentifier
DECLARE @GroupLevel integer
DECLARE @LoopLevel integer
INSERT INTO @Groups
SELECT WindoworGroupGUID, WFWG.WorkspaceGUID, g.GroupLevel + 1, 0
FROM Table_3 LMM
JOIN Table_2 WFWG
ON WFWG.WindowGroupGUID = LMM.GroupGUID
JOIN @Groups as g
ON LMM.GroupGUID = g.GroupGUID
AND LMM.WindoworGroupTypeGUID = @WindowGroupTypeGUID
WHERE EndDate IS NULL
INSERT INTO @Windows
SELECT WGMW.WindoworGroupGUID, WindoworGroupTypeGUID, g.WorkspaceGUID, LastActioned, RefreshNumber
FROM Table_3 WGMW
JOIN Table_4 WFW
ON WFW.WorkFloWindowGUID = WGMW.WindoworGroupGUID
LEFT JOIN Table_5 AWFW2
ON AWFW2.WorkFloWindowGUID = WindoworGroupGUID
AND AWFW2.Flags & 1 = 1
LEFT JOIN Table_6 UI
ON UI.AccountGUID = AWFW2.AccountGUID
JOIN @Groups as g
ON WGMW.GroupGUID = g.GroupGUID
AND WGMW.WindoworGroupTypeGUID <> @WindowGroupTypeGUID
WHERE WGMW.EndDate IS NULL
AND WGMW.WindoworGroupGUID NOT IN (SELECT WindowGUID FROM @Windows)
INSERT INTO @Windows
SELECT WE.WindowGUID, WFW.WindowTypeGUID, WFW.WorkspaceGUID, WFW.LastActioned, WFW.RefreshNumber
FROM Table_9 WE
JOIN Table_4 WFW (/*index=IX_dtblWorkFloWindows_WorkFloWindowGUID_WorkspaceGUID_WindowTypeGUID_LastActioned_RefreshNumber,*/NOLOCK)
ON WFW.WorkFloWindowGUID = WE.WindowGUID
JOIN Table_7 EH
ON EH.EventGUID = WE.WindowEventGUID
LEFT JOIN @Windows W
ON W.WindowGUID = WE.WindowGUID
AND W.WorkspaceGUID = WFW.WorkspaceGUID
WHERE WFW.WorkspaceGUID > '{00000000-0000-0000-0000-000000000000}'-- IS NOT NULL
AND W.WindowGUID IS NULL
INSERT INTO @Windows
SELECT DISTINCT WE.WindowGUID, WFW.WindowTypeGUID, LTD.ChildGUID, WFW.LastActioned, WFW.RefreshNumber
FROM Table_8 LTD
JOIN Table_9 WE
ON WE.WindowEventGUID IS NOT NULL
JOIN Table_4 WFW
ON WFW.WorkFloWindowGUID = WE.WindowGUID
JOIN Table_7 EH
ON EH.EventGUID = WE.WindowEventGUID
LEFT JOIN @Windows W
ON W.WindowGUID = WE.WindowGUID
AND W.WorkspaceGUID = LTD.ChildGUID
WHERE WFW.WorkspaceGUID IS NULL
AND LTD.ParentGUID = '{43B03D8B-1333-4BF3-BEB6-A1446698223C}'
AND W.WindowGUID IS NULL
--Start Population of Data
INSERT INTO @Results
SELECT DISTINCT W.WindowGUID,
W.WindowTypeGUID,
W.WorkspaceGUID,
COUNT(1),
SUM(CASE WHEN DueDate <= GetDate() THEN 1 ELSE 0 END),
SUM(CASE WHEN SLADate <= GetDate() THEN 1 ELSE 0 END),
SUM(CASE WHEN DATEDIFF(day, DueDate, GetDate()) = 0 THEN 1 ELSE 0 END),
SUM(CASE WHEN DueDate <= GetDate() THEN EstimatedTaskCompletion ELSE 0 END),
SUM(CASE WHEN SLADate <= GetDate() THEN EstimatedTaskCompletion ELSE 0 END),
SUM(CASE WHEN DATEDIFF(day, DueDate, GetDate()) = 0 THEN EstimatedTaskCompletion ELSE 0 END),
SUM(EstimatedTaskCompletion),
AVG(CASE WHEN DueDate <= GetDate() THEN DateDiff(mi, DueDate , GetDate()) ELSE 0 END) * 60,
MIN(CASE WHEN DueDate <= GetDate() THEN DueDate ELSE NULL END),
MAX(W.TaskLastActioned),
dbo.fnGetAverageTasksActioned(W.WindowGUID, W.WorkspaceGUID, GetDate()),
dbo.fnGetAverageTaskTimeActioned(W.WindowGUID, W.WorkspaceGUID, GetDate()),
0, --Filled in below,
GetDate()
FROM @Windows W
JOIN Table_10 L
ON W.WindowGUID = L.WorkFloWindowGUID
JOIN Table_11 T
ON L.TaskGUID = T.TaskGUID
AND T.ActionDate IS NULL
AND T.WorkspaceGUID = W.WorkspaceGUID
JOIN Table_12 TTC
ON TTC.TaskTemplateGUID = T.TaskTemplateGUID
JOIN Table_13 TC
ON TC.TemplateClassGUID = TTC.TemplateClassGUID
AND TC.TemplateClassType = 1
JOIN Table_4 WFW
ON WFW.WorkFloWindowGUID = W.WindowGUID
AND ((WFW.WorkspaceGUID = W.WorkspaceGUID)
OR (WFW.WorkspaceGUID IS NULL))
LEFT JOIN Table_14 TA (/*index=IX_dtblTaskActions_TaskGUID_ActionTypeGUID,*/NOLOCK)
ON TA.TaskGUID = T.TaskGUID
AND TA.ActionTypeGUID = @ViewDocGUID
LEFT JOIN Table_15 N
ON N.DocumentGUID = TA.Data
WHERE ((TA.ActionTypeGUID IS NULL) OR (N.DocumentGUID IS NOT NULL))
AND W.RefreshNumber = @RefreshNumber
GROUP BY W.WindowGUID, W.WindowTypeGUID, W.WorkspaceGUID, T.WorkspaceGUID
--Insert all data that isnt being refreshed
INSERT INTO @Results(R.WindowGUID,
R.WindowTypeGUID,
R.WorkspaceGUID,
R.NumberOfTasks,
R.NumberOfDueTasks,
R.SLAFailedTasks,
R.TasksDueToday,
R.DueTasksTimeOutstanding,
R.SLAFailedTasksTimeOutstanding,
R.DueTodayTimeOutstanding,
R.AllTasksTimeOutstanding,
R.DueTasksAverageAge,
R.DueTasksOldest,
R.TaskLastActioned,
R.AverageTasksPerHour,
R.AverageTaskTimePerHour,
R.WorkingHoursToClear,
R.LastRefresh)
SELECT DISTINCT [RowGUID], [RowTypeGUID], W.[WorkspaceGUID], [NumberOfTasks], [NumberOfDueTasks], [SLAFailedTasks], [TasksDueToday], [DueTasksTimeOutstanding], [SLAFailedTasksTimeOutstanding], [DueTodayTimeOutstanding], [AllTasksTimeOutstanding], [DueTasksAverageAge], [DueTasksOldest], WFWS.[TaskLastActioned], [AverageTasksPerHour], [AverageTaskTimePerHour], [WorkingHoursToClear], [LastRefreshed]
FROM Table_16 WFWS
JOIN @Windows W
ON WFWS.RowGUID = W.WindowGUID
AND WFWS.WorkspaceGUID = W.WorkspaceGUID
WHERE W.RefreshNumber <> @RefreshNumber
--Insert any rows that have not been returned in the result set
INSERT INTO @Results(R.WindowGUID,
R.WindowTypeGUID,
R.WorkspaceGUID,
TaskLastActioned,
AverageTasksPerHour,
AverageTaskTimePerHour,
LastRefresh)
SELECT DISTINCT W.WindowGUID, W.WindowTypeGUID, W.WorkspaceGUID, W.TaskLastActioned, dbo.fnGetAverageTasksActioned(W.WindowGUID, W.WorkspaceGUID, GetDate()), dbo.fnGetAverageTaskTimeActioned(W.WindowGUID, W.WorkspaceGUID, GetDate()), GetDate()
FROM @Windows W
LEFT JOIN @Results R
ON R.WindowGUID = W.WindowGUID
AND R.WorkspaceGUID = W.WorkspaceGUID
WHERE R.WindowGUID IS NULL
--Calculate the Working Hours to Clear
--Need to insert into an Aggregate table so we can correctly calculate the Working hours values
INSERT INTO @Aggregates
SELECT WindowGUID, WindowTypeGUID, WorkspaceGUID, NumberOfTasks, NumberOfDueTasks, SLAFailedTasks, TasksDueToday,
DueTasksTimeOutstanding , SLAFailedTasksTimeOutstanding , DueTodayTimeOutstanding ,AllTasksTimeOutstanding , DueTasksAverageAge ,
DueTasksOldest ,TaskLastActioned, AverageTasksPerHour ,AverageTaskTimePerHour, WorkingHoursToClear, LastRefresh
FROM @Results
UPDATE @Results
SET WorkingHoursToClear = (SELECT CASE WHEN AverageTaskTimePerHour = 0 THEN NULL ELSE ((CAST(AllTasksTimeOutstanding as decimal(10,2)) / CAST(AverageTaskTimePerHour as decimal(10,2)) *60)*60) END
FROM @Aggregates R
WHERE WindowGUID = AggWindowGUID
And WorkspaceGUID = AggWorkspaceGUID)
--Do Group Calculations
INSERT INTO @Results
SELECT WGM.GroupGUID,
@WindowGroupTypeGUID,
R.WorkspaceGUID,
SUM(NumberOfTasks),
SUM(NumberOfDueTasks),
SUM(SLAFailedTasks),
SUM(TasksDueToday),
SUM(DueTasksTimeOutstanding),
SUM(SLAFailedTasksTimeOutstanding),
SUM(DueTodayTimeOutstanding),
SUM(AllTasksTimeOutstanding),
AVG(DueTasksAverageAge),
MIN(DueTasksOldest),
MAX(TaskLastActioned),
AVG(AverageTasksPerHour),
AVG(AverageTaskTimePerHour),
SUM(WorkingHoursToClear),
MAX(LastRefresh)
FROM Table_3 WGM
JOIN @Results R
ON WGM.WindoworGroupGUID = R.WindowGUID
JOIN @Groups as g
ON WGM.GroupGUID = g.GroupGUID
WHERE WGM.EndDate IS NULL
GROUP BY WGM.GroupGUID , R.WorkspaceGUID
HAVING MAX(RefreshedAlready) = 0
UPDATE WFWS
SET [NumberOfTasks] = R.NumberOfTasks,
[NumberOfDueTasks] = R.NumberOfDueTasks,
[SLAFailedTasks] = R.SLAFailedTasks,
[TasksDueToday] = R.TasksDueToday,
[DueTasksTimeOutstanding] = R.DueTasksTimeOutstanding,
[SLAFailedTasksTimeOutstanding] = R.SLAFailedTasksTimeOutstanding,
[DueTodayTimeOutstanding] = R.DueTodayTimeOutstanding,
[AllTasksTimeOutstanding] = R.AllTasksTimeOutstanding,
[DueTasksAverageAge] = R.DueTasksAverageAge,
[DueTasksOldest] = R.DueTasksOldest,
[TaskLastActioned] = R.TaskLastActioned,
[AverageTasksPerHour] = R.AverageTasksPerHour,
[AverageTaskTimePerHour] = R.AverageTaskTimePerHour,
[WorkingHoursToClear] = CAST(R.WorkingHoursToClear as Integer),
[LastRefreshed] = R.LastRefresh
FROM @Results R
JOIN Table_16 WFWS
ON R.WindowGUID = WFWS.RowGUID
AND R.WorkspaceGUID = WFWS.WorkspaceGUID
INSERT INTO Table_16([UniqueGUID], [RowGUID], [RowTypeGUID], [WorkspaceGUID], [NumberOfTasks], [NumberOfDueTasks], [SLAFailedTasks], [TasksDueToday], [DueTasksTimeOutstanding], [SLAFailedTasksTimeOutstanding], [DueTodayTimeOutstanding]
, [AllTasksTimeOutstanding], [DueTasksAverageAge], [DueTasksOldest], [TaskLastActioned], [AverageTasksPerHour], [AverageTaskTimePerHour], [WorkingHoursToClear], [LastRefreshed])
SELECT NewID(),
R.WindowGUID,
R.WindowTypeGUID,
R.WorkspaceGUID,
R.NumberOfTasks,
R.NumberOfDueTasks,
R.SLAFailedTasks,
R.TasksDueToday,
R.DueTasksTimeOutstanding,
R.SLAFailedTasksTimeOutstanding,
R.DueTodayTimeOutstanding,
R.AllTasksTimeOutstanding,
R.DueTasksAverageAge,
R.DueTasksOldest,
R.TaskLastActioned,
R.AverageTasksPerHour,
R.AverageTaskTimePerHour,
R.WorkingHoursToClear,
R.LastRefresh
FROM @Results R
LEFT JOIN Table_16 WFWS
ON R.WindowGUID = WFWS.RowGUID
AND R.WorkspaceGUID = WFWS.WorkspaceGUID
WHERE WFWS.RowGUID IS NULL
SELECT distinct * FROM @Results
SET NOCOUNT OFF
Read part 2
No comments:
Post a Comment