Vallavan J Vallavan J - 23 days ago 8
MySQL Question

get enter hours from the table using mysql

this the query:

How to get the value of three select query
Name,HOURS ENTERED',HOURS APPROVED,HOURS REJECTED

SELECT e.ENAME 'NAME', SUM(l.HOURS) 'HOURS ENTERED' FROM labors l, emps e
WHERE l.EmpsID = e.EmpsID AND APPROVED = 'N' AND l.EmpsID = 'AGIUCL01'
AND l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05'
union
(SELECT e.ENAME 'NAME', SUM(l.HOURS) 'HOURS APPROVED' FROM labors l, emps e
WHERE l.EmpsID = e.EmpsID AND APPROVED = 'A' AND l.EmpsID = 'AGIUCL01'
AND l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05')
union
SELECT e.ENAME 'NAME', SUM(l.HOURS) 'HOURS REJECTED' FROM labors l,empse
WHERE l.EmpsID = e.EmpsID AND APPROVED = 'R'
AND l.EmpsID = 'AGIUCL01'
AND l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05'


Query:

SELECT e.ENAME 'NAME',
SUM(CASE WHEN l.APPROVED='N' THEN l.HOURS ELSE 0 END) 'HOURS ENTERED',
SUM(CASE WHEN l.APPROVED='A' THEN l.HOURS ELSE 0 END) 'HOURS APPROVED',
SUM(CASE WHEN l.APPROVED='R' THEN l.HOURS ELSE 0 END) 'HOURS REJECTED'
FROM labors l ,emps e
where l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05' and
e.TERM_DATE < e.REHIRE_DATE
or e.TERM_DATE IS NULL
group by (e.empsid)


I am executing the query my result from the table is this.

NAME HOURS ENTERED HOURS APPROVED HOURS REJECTED
CLAUDINEAGIUS 292.00 39.00 20.00
TODD MICHAEL ALDAMA 292.00 39.00 20.00
STEVE ALMGREN 292.00 39.00 20.00
SHAWN ANDERSON 292.00 39.00 20.00
JARETT BARNETT 292.00 39.00 20.00


But I am not getting the every user entered the current time lines.

Answer

You can use conditionals in the SUM:

SELECT e.ENAME 'NAME',
  SUM(CASE WHEN APPROVED='N' THEN l.HOURS ELSE 0 END) 'HOURS ENTERED',
  SUM(CASE WHEN APPROVED='A' THEN l.HOURS ELSE 0 END) 'HOURS APPROVED',
  SUM(CASE WHEN APPROVED='R' THEN l.HOURS ELSE 0 END) 'HOURS REJECTED'
FROM labors l, emps e 
WHERE l.EmpsID = e.EmpsID
  AND l.EmpsID = 'AGIUCL01'
  AND l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05'