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