SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Attendance report Using Mysql Query

    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 my database.

    I have following tables.
    Code:
    Employee Table with Columns
    
        emp_id  emp_Name   Joining_Date
         1      john         11-01-2012
         2      Scott        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 person will select year and month and he should be able to get attendance report in following format Example : suppose user selects year as '2011' and month as 'Dec' then output should be

    Code:
    Employee year  Month    1  2   3   4   5   6    7.... 
     John      2011   Nov     Y  Y   Y   Y   Y   L    S....
     Scott     2011   Nov     Y  Y   L   M   Y   L    S
    here in output 1,2,3.... are days from 0-30 for a month which we can write using 'case'

    Consider if employee is present on day show its status as 'Y' else L else if he gone to any customer like doctor,chemist,then replace it with 'S'.

    So how should I write a query to achieve this output?? any suggestions will be helpful for me....

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What have you got so far?

  3. #3
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think we need to use a lot case expression here..

    so do you have any example where case expression is used in best way so far....


Tags for this Thread

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
  •