Click here to Skip to main content
14,770,691 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi
I wrote the below query to get total count of particular user to produce the production report

Can someone please help me on this.

What I have tried:

My Sql Query is:
COALESCE(SUM(CONVERT(INT, b.image_count)), 0) AS Coding,
    (COALESCE(SUM(CONVERT(INT, e.image_count)), 0)/4) AS QC,
    (COALESCE(SUM(CONVERT(INT, q.duration*5.0)), 0)) AS QA,

 (COALESCE(SUM(CONVERT(INT, b.image_count)), 0)) +(COALESCE(SUM(CONVERT(INT, e.image_count)), 0)/4) + (COALESCE(SUM(CONVERT(INT, q.duration*5.0)), 0)) 
     AS Total

FROM 
    test a
    
    LEFT JOIN test b ON a.resources = b.resources and a.testid_PK=b.testid_PK AND (b.Work_area='Coding') and YEAR(b.dat_e) = '2017' AND MONTH(b.dat_e) =  '04' and b.resources='Sam' 
    LEFT JOIN test e ON a.resources = e.resources and a.testid_PK=e.testid_PK AND (e.Work_area='QC') and YEAR(e.dat_e) = '2017' AND MONTH(e.dat_e) = '04'  and e.resources='Sam'
    LEFT JOIN test q ON a.resources = q.resources and a.testid_PK=q.tesstid_PK AND (q.Work_area='QA') and YEAR(q.dat_e) ='2017' AND MONTH(q.dat_e) = '04' and q.resources='Sam'

and I am getting results like below:
resources    Coding QC QA Total
Sam	     50      10  15   75
Rita	     0        0    0    0
Mary	     0        0    0    0
sharo	     0        0    0    0

and I want the output like below
resources    Coding QC QA Total
Sam	     50      10  15   75

Mt table data is like below
testid_PK	dat_e	resources	work_area	image_count	doc_count	status	duration	fieldscount
10926	4/1/2017	Sam	Coding	0	14	Completed	0	8
10927	4/1/2017	Mary	Coding	0	28	Completed	0	8
10928	4/1/2017	Sam	Coding	0	46	Completed	0	8
10929	4/1/2017	Rita	Coding	0	82	Completed	0	8
10930	4/2/2017	Sam	Coding	0	16	Completed	0	8
10931	4/2/2017	Mary	Coding	0	22	Completed	0	8
10932	4/2/2017	Sam	Coding	0	66	Completed	0	8
10933	4/2/2017	Mary	Coding	0	46	Completed	0	8
10934	4/2/2017	sharo	QC	0	160	Completed	0	8
10935	4/2/2017	Rita	QC	0	25	Completed	0	8
10936	4/3/2017	Rita	QC	0	125	Completed	0	8
11284	4/4/2017	sharo	Coding	500	0	Completed	0	0
11285	4/4/2017	Sam	Coding	200	1	Completed	0	0
11286	4/4/2017	Rita	Coding	101	0	Completed	0	0
11287	4/4/2017	sharo	QA	801	0	Completed	10	0
11288	4/4/2017	Sam	Coding	0	69	Completed	0	12
11289	4/4/2017	sharo	Coding	0	70	Completed	0	12
Posted
Updated 2-May-17 2:43am
v4
Comments
vivvicks 2-May-17 0:08am
   
is it possible to paste input data or table data in which u r trying to query
SukirtiShetty 2-May-17 0:45am
   
I have updated table data in my Question panel.
vivvicks 2-May-17 1:47am
   
will u paste test table data also.
vivvicks 2-May-17 2:14am
   
are "test" and "work" two different tables? as u have mentioned in query..if yes then will u paste work table data pls?
SukirtiShetty 2-May-17 2:19am
   
yes it is test table only i have updated in question. I am using single table here

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
   
v2
What is your exact requirement? Kindly elaborate. GROUP BY can do the trick instead of such a complex query. However, your elaboration may help for the response. Kindly give example of how the desired output should look like.
   
Comments
vivvicks 2-May-17 2:47am
   
exactly
CHill60 2-May-17 6:37am
   
Please do not post questions or comments as solutions. It removes the post from the list of unanswered questions, meaning fewer people will see the question.
Kindly remove this solution

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