mysqli - get enter hours from the table using mysql -
this question has answer here:
this query:
how value of 3 select query name,hours entered',hours approved,hours rejected
select e.ename 'name', sum(l.hours) 'hours entered' labors l, emps e l.empsid = e.empsid , approved = 'n' , l.empsid = 'agiucl01' , l.date_work between '2016-10-30' , '2016-11-05' union (select e.ename 'name', sum(l.hours) 'hours approved' labors l, emps e l.empsid = e.empsid , approved = 'a' , l.empsid = 'agiucl01' , l.date_work between '2016-10-30' , '2016-11-05') union select e.ename 'name', sum(l.hours) 'hours rejected' labors l,empse l.empsid = e.empsid , approved = 'r' , l.empsid = 'agiucl01' , l.date_work between '2016-10-30' , '2016-11-05' query:
select e.ename 'name', sum(case when l.approved='n' l.hours else 0 end) 'hours entered', sum(case when l.approved='a' l.hours else 0 end) 'hours approved', sum(case when l.approved='r' l.hours else 0 end) 'hours rejected' labors l ,emps e l.date_work between '2016-10-30' , '2016-11-05' , e.term_date < e.rehire_date or e.term_date null group (e.empsid) i executing query result table 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 not getting every user entered current time lines.
you can use conditionals in sum:
select e.ename 'name', sum(case when approved='n' l.hours else 0 end) 'hours entered', sum(case when approved='a' l.hours else 0 end) 'hours approved', sum(case when approved='r' l.hours else 0 end) 'hours rejected' labors l, emps e l.empsid = e.empsid , l.empsid = 'agiucl01' , l.date_work between '2016-10-30' , '2016-11-05'
Comments
Post a Comment