Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Location
    Pune,INDIA
    Posts
    6

    Unanswered: Attendance Report using MySql

    I want to write a query to generate attendance report of employee. First I will tell you how the presence of employee is stored in database.

    I have following tables.

    Code:
    Employee Table with Columns
    
        emp_id  emp_Name  Joining_Date
         1        L         11-01-2012
         2        M         12-01-2012
    
    
    Holiday Table
    
    Holiday_Name         Date 
    Chrismas          25-12-2012  
    Dushera           08-03-2012
    Independance Day  15-08-2012
    
    Leave Table
    
    Subject  from_Date     to_Date      Emp_Id     status 
    PL       02-01-2012    04-01-2012      1       Approved
    CL       11-01-2012    12-01-2012      2       Declined   
    
    
    Doctor Table
    
    Subject   Call_Date    call_Done_By(emp_id)
     Call     15-01-2012      1
     CA       21-02-2012      2
    
    Chemist Table
    Subject   Call_Date    call_Done_By(emp_id)
     Chemist   1-02-2012     2
     Texo      21-03-2012      1
    If employee is visited to doctor or chemist,that particular date is stored in that particular doctor or chemist table with employee_id

    Now I want output format like this way..................

    Code:
    Employee    Month    1  2   3   4   5   6    7.... 
     Name       Nov11    Y  Y   Y   Y   Y   L    S....
     Name 1     Dec11    Y  Y   Y   Y   Y   Y    S....
    what will be the possible solutions for it?
    Last edited by pravingate07; 02-06-12 at 06:05.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you fix the underlying problems with the schema, or are you required to use bubble gum and bailing wire to make the existing schema work as best you can?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2012
    Location
    Pune,INDIA
    Posts
    6

    Attendance Report using MySql

    Yes I need to achieve this with existing schema.. as I have no control on schema.

  4. #4
    Join Date
    Feb 2012
    Location
    Pune,INDIA
    Posts
    6
    If anyone is getting any problem to understand my question, let me know.
    I 'll try tel in another way.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pravingate07 View Post
    Now I want output format like this way..................
    you will have to set up a CASE expression for each day of the month
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2012
    Location
    Pune,INDIA
    Posts
    6

    Attendance Report using MySql

    yes I am also thinking in same way.. But you have any example that have used best case expression so far....

Tags for this Thread

Posting Permissions

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