Jump to content

how to make this Query with 3 table


rezbin

Recommended Posts

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.

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.