Sql查询显示月份明智的报告 [英] Sql query to display month wise report

问题描述

您好b $ b我正在尝试根据年份和月份以及用户名显示报告。 
 
我的查询有效，但它显示的所有日期都不是条件
 
 
任何人都可以帮我解决这个问题

我的尝试：

 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总计
 
 FROM 
工作a 
 LEFT JOIN工作d on a.resources = d.resources和a.id_PK = d.id_PK AND（d.work_area ='其他'）AND YEAR（d.dat_e）='2017'和月（d.dat_e）='12'和d.resources ='Mary'
 LEFT JOIN工作b ON a.resources = b。资源和a.id_PK = b.id_PK AND（b.Work_area ='Co ding'）和YEAR（b.dat_e）='2017'和月（b.dat_e）='12'和b.resources ='Mary'
 LEFT JOIN工作e on a.resources = e.resources and a.id_PK = e.id_PK AND（e.Work_area ='Coding2'）和YEAR（e.dat_e）='2017'和月（e.dat_e）='12'和e.resources ='Mary'
 LEFT JOIN工作q ON a.resources = q.resources和a.id_PK = q.id_PK AND（q.Work_area ='Coding3'）和YEAR（q.dat_e）='2017'和月（q.dat_e）= '12'和q.resources ='Mary'
 
 GROUP BY 
 a.dat_e 

输出为：

 dat_e Coding_Stage1 Coding_Stage2 Coding_Stage3总计
 12/9/2016 0 0 0 0 
 11/16/2016 0 0 0 0 
 12/4/2017 20 0 50 70 
 2017/3/19 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 

数据库数据：

 ID dat_e resources work_area image_count状态持续时间
 1011 12/1/2017 Sam Coding 478完成0 
 1012 12/1/2017 John Coding2 105完成0 
 1013 12/4/2017 Mary Coding 290已完成0 
 1014 12/4/2017 John Coding 290完成0 
 1015 12/6/2017 John Coding 178完成0 
 1016 12/6/2017 Peter Coding 365完成0 
 1017 12/8/2017 Mary Coding2 332已完成0 
 1018 12/8/2017 Peter QA 2333已完成0 
 1019 12/11/2017 Peter Coding 256已完成0 
 1020 12/12 / 2017 John Coding 253已完成0 
 1021 12/13/2017 Sam Coding 253已完成0 
 1022 12/14/2017 Mary Coding3 253已完成30 
 1023 12/14/2017 Sam QA 1268已完成0 
 1024 12/14/2017 Peter Coding 1012已完成0 
 1025 1/1/2018 Mary Coding3 150已完成50 
 1026 1/1/2018 John Coding 255已完成0 
 1027 1/1/2018 John Coding3 199完成0 
 1028 1/3/2018 Peter Coding 1015 Comp出租0 
 1029 1/3/2018 Mary Coding2 999已完成0 
 1030 1/3/2018 Peter Coding 798已完成0 

解决方案

你没有关于结果集中应该包含哪些记录的条件，所以包括所有记录（分组不改变它）...

请参考有关SQL WHERE CLAUSE的帮助页面...

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

解决方案

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...

这篇关于Sql查询显示月份明智的报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！