rezbin Posted June 26, 2011 Share Posted June 26, 2011 Hi, can anyone show me the procedure to make this happen, i am new on this. emp_id--Name 1-------AA 2-------AB 3-------AC 4-------AD 5-------AE 6-------AF 7-------AZ 2)table job_done emp_id --- date - --job_done --score 1 - - - 22-06-2011 -------1-------50 3 - - - 23-06-2011--------1-------25 5 - - - 24-06-2011--------1-------70 1 - - - 25-06-2011--------1-------50 3 - - - 26-06-2011--------1-------25 3)table job_failed emp_id - -- date -- job_failed 2 - - - 22-06-2011 -------1 4 - - - 24-06-2011 -------1 6 - - - 26-06-2011 -------1 5 - - - 21-06-2011 -------1 Now how can i get result like Name - -job_done - job_failed -score - job_handled - success_rate AA---------2----------0----------50-------- 2 -----------100% AB---------0----------1----------0 -------- 1 ----------- 0% AC---------2----------0----------25-------- 2 -----------100% AD---------0----------1----------0 -------- 1 ----------- 0% AE---------1----------1----------70-------- 2 ----------- 50% AF---------0----------1----------0 -------- 1 ----------- 0% 1)here date will not visible but i must able to search by date from both job_done and failed date by one selected range but i'm only can manage with one date either table 2 or 3 . 2)if a employee like 'AZ' not give any job will exclude from report. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted June 27, 2011 Share Posted June 27, 2011 transform your tables job_done and job_failed in only one table for better design,,, after that your queries will be easier Quote Link to comment Share on other sites More sharing options...
rezbin Posted June 27, 2011 Author Share Posted June 27, 2011 Hi mikosiko, this table layout was created and used by a software so i cant do that.i know this would be better. please help to get the 2nd option. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted June 27, 2011 Share Posted June 27, 2011 well... a couple LEFT JOIN and using COUNT() (or SUM) will give you the results... with exception of the "score" column because it will lose meaning at the aggregation time... work on some code and came back if you still have doubts. Quote Link to comment Share on other sites More sharing options...
rezbin Posted June 27, 2011 Author Share Posted June 27, 2011 Hello Mikosiko, Here is my Query's received from one great. and only resolved half of problem select employee.name, ifnull(done,0) done,ifnull(failed,0) failed,ifnull(score,0) score from employee left join (select emp_id,sum(job_done) done,avg(score) score from job_done group by emp_id) done using(emp_id) left join (select emp_id, sum(job_failed) failed from job_failed group by emp_id) failed using(emp_id) order by employee.name; Here im unable to add sum of done+failed = total, done/total*100= xx%. and finally combine both date in a single column so that i can search by date. can you please give some code to finish it? Thanks Quote Link to comment Share on other sites More sharing options...
mikosiko Posted June 27, 2011 Share Posted June 27, 2011 here is your fish: .... replace any wrong column name with your own, and with the proper dates that you want (or variables) select a.name, COUNT(b.job_done) AS job_done, COUNT(c.job_failed) AS job_failed, COUNT(b.job_done) + COUNT(c.job_failed) AS job_handled, (COUNT(b.job_done) * 100) / (COUNT(b.job_done) + COUNT(c.job_failed)) AS success_rate FROM employee AS a LEFT JOIN job_done AS b ON b.emp_id = a.emp_id AND b.fecha between '2011-06-21' AND '2011-06-26' LEFT JOIN job_failed AS c ON c.emp_id = a.emp_id AND c.fecha between '2011-06-21' AND '2011-06-26' GROUP BY a.name HAVING job_handled > 0; anything else is for you to work on. Quote Link to comment Share on other sites More sharing options...
rezbin Posted June 27, 2011 Author Share Posted June 27, 2011 Hello Mikosilko, Thank u for you quick response. having problem with ur code. error showing "Unknown column 'b.fetcha' in 'on clause'" Quote Link to comment Share on other sites More sharing options...
rezbin Posted June 27, 2011 Author Share Posted June 27, 2011 Dear mikosiko, Thank you very much. it works now. really appreciating help. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.