Click here to Skip to main content
15,879,095 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am getting stuck with a query getting the sum of counts.

I have a situation where I have to get every department and sum up all employee tasks:

Currently, my output is as follows:

Department	Total Tasks	Num Completed	Num OVERDUE
Asset Management	0	0	0
Asset Management	40	0	40
Budget and Treasury	1	0	1
Budget and Treasury	14	8	6

I need to show as below:
Department	Total Tasks	Num Completed	Num OVERDUE
Asset Management	40	0	40
Budget and Treasury	15	8	7


What I have tried:

SQL
declare @DateFrom datetime = '2023-08-01'
declare @DateTo datetime = '2023-09-27'

SELECT     distinct(u.Department)

,(select count(pt.to_resource) from pcs_task pt with (nolock)  where p.pcs_user_id = pt.to_resource and  DATEADD(DAY, -30, @DateTo) >  pt.date_closed and pt.due_date <= (@DateTo) and pt.date_alloc >= (@DateFrom) and date_closed <= @DateTo and pt.tsk_status = 'COMPLETE') +
					 (select count(pt.to_resource) from pcs_task pt with (nolock) where p.pcs_user_id = pt.to_resource and DATEADD(DAY, -60, @DateTo) >  pt.date_closed and pt.due_date <= (@DateTo) and pt.date_alloc >= (@DateFrom) and date_closed <= @DateTo and pt.tsk_status = 'COMPLETE') +
(select count(pt.to_resource) from pcs_task pt with (nolock) where p.pcs_user_id = pt.to_resource and DATEADD(DAY, 30, @DateTo) >=  pt.date_alloc and pt.due_date <= (@DateTo) and pt.date_alloc >= (@DateFrom) and pt.tsk_status <> 'COMPLETE') + 
					(select count(pt.to_resource) from pcs_task pt with (nolock) where p.pcs_user_id = pt.to_resource and DATEADD(DAY, 60, pt.due_date) >= pt.date_alloc and pt.due_date <= (@DateTo) and pt.date_alloc >= (@DateFrom) and pt.tsk_status <> 'COMPLETE') as [Total Tasks]

, (select count(pt.to_resource) from pcs_task pt with (nolock) where p.pcs_user_id = pt.to_resource and  DATEADD(DAY, -30, @DateTo) >  pt.date_closed and pt.due_date <= (@DateTo) and pt.date_alloc >= (@DateFrom) and date_closed <= @DateTo and pt.tsk_status = 'COMPLETE') +
					 (select count(pt.to_resource) from pcs_task pt with (nolock) where p.pcs_user_id = pt.to_resource and DATEADD(DAY, -60, @DateTo) >  pt.date_closed and pt.due_date <= (@DateTo) and pt.date_alloc >= (@DateFrom) and date_closed <= @DateTo and pt.tsk_status = 'COMPLETE') as [Num Completed]

,(select count(pt.to_resource) from pcs_task pt with (nolock) where p.pcs_user_id = pt.to_resource and DATEADD(DAY, 30, @DateTo) >=  pt.date_alloc and pt.due_date <= (@DateTo) and pt.date_alloc >= (@DateFrom) and pt.tsk_status <> 'COMPLETE') + 
					(select count(pt.to_resource) from pcs_task pt with (nolock) where p.pcs_user_id = pt.to_resource and DATEADD(DAY, 60, pt.due_date) >= pt.date_alloc and pt.due_date <= (@DateTo) and pt.date_alloc >= (@DateFrom) and pt.tsk_status <> 'COMPLETE') as [Num OVERDUE]

FROM         pcs_user p
					 INNER JOIN Users u with (nolock) on u.[Reference Number] = p.obj_id
WHERE     (p.user_status = 'Active') AND p.pcs_user_id <> 'ADMIN'-- AND  (u.Department in (@Department))
order BY u.Department--, p.pcs_user_id


I basically have to get the sum of all users per department.

SUM of Total Tasks, Num COMPLETED and num OVERDUE
WHEN I add SUM function, I get the below:
Sum function - cannot perform an aggregate function on an expression containing an aggregate or a subquery
Posted
Updated 2-Oct-23 3:33am
v3

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); or
    • date_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:
SQL
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
;
 
Share this answer
 
WITH intermediate(department, total, completed, overdue) 
AS (SELECT ... )  -- query
SELECT department, SUM(total), SUM(completed), SUM(overdue)
FROM intermediate
GROUP BY department
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900