-1

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Vallavan J
  • 49
  • 6

1 Answers1

0

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'
Sami Kuhmonen
  • 30,146
  • 9
  • 61
  • 74