Click here to Skip to main content
15,919,931 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi 
I am trying to display report based on year and month and also user name.

My Query worked but it displays all the dates which is other than in condition


Anyone please help me to resolve this issue


What I have tried:

SELECT 
    a.dat_e,
     COALESCE	(SUM(CONVERT(INT, b.image_count)), 0) AS Coding_Stage1,
    (COALESCE(SUM(CONVERT(INT, e.image_count)), 0)/4) AS Coding_Stage2,
    (COALESCE(SUM(CONVERT(INT, q.duration*6.25)), 0)) AS Coding_Stage3,
    (COALESCE(SUM(CONVERT(INT, b.image_count)), 0)) +(COALESCE(SUM(CONVERT(INT, e.image_count)), 0)/4) + (COALESCE(SUM(CONVERT(INT, q.duration*6.25)), 0))
     AS Total
        
FROM
    work a
    LEFT JOIN work d ON a.resources = d.resources and a.id_PK=d.id_PK AND (d.work_area='other') AND YEAR(d.dat_e) = '2017' AND MONTH(d.dat_e) = '12' and d.resources='Mary'
    LEFT JOIN work b ON a.resources = b.resources and a.id_PK=b.id_PK AND (b.Work_area='Coding') and YEAR(b.dat_e) = '2017' AND MONTH(b.dat_e) = '12' and b.resources='Mary'
    LEFT JOIN work e ON a.resources = e.resources and a.id_PK=e.id_PK AND (e.Work_area='Coding2') and YEAR(e.dat_e) = '2017' AND MONTH(e.dat_e) = '12' and e.resources='Mary'
    LEFT JOIN work q ON a.resources = q.resources and a.id_PK=q.id_PK AND (q.Work_area='Coding3') and YEAR(q.dat_e) = '2017' AND MONTH(q.dat_e) = '12' and q.resources='Mary'
   
GROUP BY
    a.dat_e



Output is:


dat_e	Coding_Stage1	Coding_Stage2	Coding_Stage3	Total
12/9/2016	0	0	0	0
11/16/2016	0	0	0	0
12/4/2017	20	0	50	70
3/19/2017	0	0	0	0
12/8/2017	10	10	0	20
7/16/2016	0	0	0	0
12/14/2017	0	10	55	65
5/31/2016	0	0	0	0
1/5/2018	0	0	0	0
3/10/2017	0	0	0	0
4/2/2017	0	0	0	0
6/4/2017	0	0	0	0
1/6/2017	0	0	0	0




database data:

ID	dat_e	resources	work_area	image_count	Status	duration
1011	12/1/2017	Sam	Coding	478	Completed	0
1012	12/1/2017	John	Coding2	105	Completed	0
1013	12/4/2017	Mary	Coding	290	Completed	0
1014	12/4/2017	John	Coding	290	Completed	0
1015	12/6/2017	John	Coding	178	Completed	0
1016	12/6/2017	Peter	Coding	365	Completed	0
1017	12/8/2017	Mary	Coding2	332	Completed	0
1018	12/8/2017	Peter	QA	2333	Completed	0
1019	12/11/2017	Peter	Coding	256	Completed	0
1020	12/12/2017	John	Coding	253	Completed	0
1021	12/13/2017	Sam	Coding	253	Completed	0
1022	12/14/2017	Mary	Coding3	253	Completed	30
1023	12/14/2017	Sam	QA	1268	Completed	0
1024	12/14/2017	Peter	Coding	1012	Completed	0
1025	1/1/2018	Mary	Coding3	150	Completed	50
1026	1/1/2018	John	Coding	255	Completed	0
1027	1/1/2018	John	Coding3	199	Completed	0
1028	1/3/2018	Peter	Coding	1015	Completed	0
1029	1/3/2018	Mary	Coding2	999	Completed	0
1030	1/3/2018	Peter	Coding	798	Completed	0
Posted
Updated 18-Jan-18 16:59pm
v2
Comments
CHill60 16-Jan-18 6:18am    
Do you have some sample data from the table [work] you could share and also what is it (in words) that you are trying to show on the report
SukirtiShetty 18-Jan-18 23:01pm    
I am trying to display monthwise report for the particular resource but it displays all the dates in database so please help me in this.

I have updated database data in my question

1 solution

You have no condition on what records should be included in result set so all included (grouping does not changing that)...
Please refer to the help page about SQL WHERE CLAUSE...
 
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