First point to note, when I run your query against the data you have provided I do not get the results you claim to get. Nor do you.
Secondly, you should be storing image_count, docs_count, fieldscount and duration as numeric column types, so there is no need to use
CONVERT(INT,...
You have some fairly complex self-joins there. The whole lot could be simplified by using either a sub-query or a Common Table Expression.
Consider this sub-query (I'm using 'sharo' as the example as there are 3 types of work_areas for that resource
declare @res nvarchar(120) = 'sharo'
select resources, work_area, sum(image_count) as imgs, sum(doc_count) as docs, sum(duration) as dur
from test
WHERE YEAR(dat_e) = 2017 AND MONTH(dat_e) = 4 and resources = @res
group by resources, work_area
That will give the results
resources work_area imgs docs dur
sharo Coding 500 70 0
sharo QA 801 0 10
sharo QC 0 160 0
(I haven't done any of the division by 4 or multiplication by 5 not just because I honestly do not understand your algorithm but because we don't want to do that yet.
Many people would now suggest using a PIVOT to get the data from rows into columns, but consider that Common Table Expressions can be treated like a table, so we can do a self join to that CTE for each of the work_area
declare @res nvarchar(120) = 'sharo'
;WITH CTE AS
(
select resources, work_area, sum(image_count) as imgs, sum(doc_count) as docs, sum(duration) as dur
from test
WHERE YEAR(dat_e) = 2017 AND MONTH(dat_e) = 4 and resources = @res
group by resources, work_area
)
SELECT C1.resources,
ISNULL(C1.imgs,0) AS CodingImages, ISNULL(C1.docs,0) AS CodingDocs, ISNULL(C1.dur,0) AS CodingDur,
ISNULL(C2.imgs,0) AS QCImages, ISNULL(C2.docs,0) AS QCDocs, ISNULL(C2.dur,0) AS QCDur,
ISNULL(C3.imgs,0) AS QAImages, ISNULL(C3.docs,0) AS QADocs, ISNULL(C3.dur,0) AS QADur
FROM CTE C1
LEFT JOIN CTE C2 ON C1.resources = C2.resources AND C2.work_area = 'QC'
LEFT JOIN CTE C3 ON C2.resources = C3.resources AND C3.work_area = 'QA'
WHERE C1.work_area = 'Coding'
Which presents the results
sharo 500 70 0 0 160 0 801 0 10
Note I've used
ISNULL rather than
COALESCE. The latter is slightly less performant when there are only two possible values involved.
The other nice thing about CTE's is that you can have multiple CTEs within the same query like this
declare @res nvarchar(120) = 'Sam'
;WITH CTE AS
(
select resources, work_area, sum(image_count) as imgs, sum(doc_count) as docs, sum(duration) as dur
from test
WHERE YEAR(dat_e) = 2017 AND MONTH(dat_e) = 4 and resources = @res
group by resources, work_area
), CTE2 AS
(
SELECT C1.resources,
ISNULL(C1.imgs,0) AS CodingImages, ISNULL(C1.docs,0) AS CodingDocs, ISNULL(C1.dur,0) AS CodingDur,
ISNULL(C2.imgs,0) AS QCImages, ISNULL(C2.docs,0) AS QCDocs, ISNULL(C2.dur,0) AS QCDur,
ISNULL(C3.imgs,0) AS QAImages, ISNULL(C3.docs,0) AS QADocs, ISNULL(C3.dur,0) AS QADur
FROM CTE C1
LEFT JOIN CTE C2 ON C1.resources = C2.resources AND C2.work_area = 'QC'
LEFT JOIN CTE C3 ON C2.resources = C3.resources AND C3.work_area = 'QA'
WHERE C1.work_area = 'Coding'
)
SELECT resources, CodingImages AS Coding,QCImages/4 AS QC, QADur*5.0 AS QA,
CodingImages + QCImages/4 + QADur*5.0 AS Total
FROM CTE2
That query returns the same results as your original (although I have omitted the WHERE clause - you will have to put it back in for Year and Month). Again note that the results you claim to be expecting do not match the data you provided.
[EDIT]I came back to this. With the CTE solution working I tried to find a way to simplify it all and came up with this instead:
declare @res nvarchar(120) = 'Sam'
SELECT resources,
SUM(CASE WHEN work_area = 'Coding' THEN ISNULL(image_count,0) ELSE 0 END) AS CodingImages,
SUM(CASE WHEN work_area = 'QC' THEN ISNULL(image_count,0) ELSE 0 END) / 4 AS QCImages,
SUM(CASE WHEN work_area = 'QA' THEN ISNULL(duration,0) ELSE 0 END) * 5.0 AS QADur
FROM test
WHERE YEAR(dat_e) = 2017 AND MONTH(dat_e) = 4 and resources = @res
GROUP BY resources
Be aware that I haven't tested this to the same level I tested my earlier solution
Submit an article or tip