SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to make this Query

    Hi,
    i have 3 table
    1)table employee
    emp_id--Name
    1-------AA
    2-------AB
    3-------AC
    4-------AD
    5-------AE
    6-------AF

    2)table job_done
    emp_id--job_done --score
    1--------1-------50
    3--------1-------25
    5--------1-------70
    1--------1-------50
    3--------1-------25

    3)table job_failed
    emp_id -- job_failed
    2------------1
    4------------1
    6------------1
    5------------1

    Now how can i get result like
    Name job_done , job_failed, score
    AA---------2----------0----------50
    AB---------0----------1----------0
    AC---------2----------0----------25
    AD---------0----------1----------0
    AE---------1----------1----------70
    AF---------0----------1----------0



    Thanks in advance to get ride on it.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    why did AA and AC do the same job more than once? and why aren't you showing the combined score in your results?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937
    Thanks for ur reply. it just an example. here 1 is not a job name its a quantity of job given whether it failed or done. my software using this pattern so i cant change table format.i just want to generate report like i want.

  4. #4
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


    Thanks in advance to get ride on it.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •