Start by removing those
with (nolock) hints:
Bad habits : Putting NOLOCK everywhere[
^]
You've got some redundant comparisons in your query - for example, "completed" requests check that:
date_closed <= @DateTo; and either:
date_closed < DATEADD(DAY, -30, @DateTo); ordate_closed < DATEADD(DAY, -60, @DateTo)
You can simplify that to one condition:
date_closed < DATEADD(DAY, -30, @DateTo). That way, you won't double-count the tasks which were closed 61+ days ago.
You'll need to use a sub-query or common table expression to get the totals for each user, then sum them up by department. For example, try something like this:
WITH cteTasks As
(
SELECT
to_resource,
SUM(CASE
WHEN tsk_status = 'COMPLETE' And date_closed < DATEADD(day, -30, @DateTo) THEN 1
ELSE 0
END) As NumCompleted,
SUM(CASE
WHEN tsk_status = 'COMPLETE' THEN 0
WHEN date_alloc <= DATEADD(day, 30, @DateTo) THEN 1
WHEN date_alloc <= DATEADD(day, 60, due_date) THEN 1
ELSE 0
END) As NumOverdue
FROM
pcs_task
WHERE
due_date <= @DateTo
And
date_alloc >= @DateFrom
GROUP BY
to_resource
)
SELECT
u.Department,
SUM(t.NumCompleted + t.NumOverdue) As [Total Tasks],
SUM(t.NumCompleted) As [Num Completed],
SUM(t.NumOverdue) As [Num OVERDUE]
FROM
pcs_user p
INNER JOIN Users u on u.[Reference Number] = p.obj_id
LEFT JOIN cteTasks t
WHERE
p.user_status = 'Active'
And
p.pcs_user_id <> 'ADMIN'
GROUP BY
u.Department
ORDER BY
u.Department
;
Submit an article or tip