CODEWITHSUNDEEP
sqlcrystal-reports
Raihan
Raihan

Reputation: 105

SQL Server Query for employee wise monthly attendance report

I have a database with structure like

users:

    usr_id
    usr_fname
    usr_lname
    usr_uname
    usr_pass

organizarion:

    org_id
    org_name
    org_address
    org_tel
    org_web
    org_email
    org_cat
    org_desc

departments:

    dpt_id
    dpt_name
    dpt_desc

sections:

    sec_id
    sec_dpt_id
    sec_name
    sec_desc

designations:

    dsg_id
    dsg_sec_id
    dsg_name
    dsg_desc

employee:

    emp_id
    emp_fname
    emp_lname
    emp_phone
    emp_email
    emp_addr
    emp_join_dt
    emp_salary
    emp_card_no
    emp_dsg_id
    emp_sft_id

shifts:

    sft_id
    sft_name
    sft_from
    sft_to
    sft_desc

leaves:

    lev_id
    lev_emp_id
    lev_frm
    lev_to
    lev_desc

holidays:

    hld_id
    thl_tp_id
    hld_st_dt
    hld_end_dt
    hld_cmnt

holiday_types:

    hld_tp_id
    hld_tp_name
    hld_tp_desc

attendance:

    att_id
    att_emp_id
    att_time
    att_date
    att_dir

Can anyone help me querying to produce employee wise monthly attendance report where in time and out time for each date and "absent" would be written for absent days will be available?

My report will be like this:

Date        Attendance     Shift    In time      Out time
---------------------------------------------------------------------
   1        Present          A     12:40 PM      06:40 PM
   2        Absent           A       N/A           N/A
   3
   .
   .
   .
  31        Present          B     07:00 PM      11:00 PM
---------------------------------------------------------------------
Total

I have done so far:

select att_date, att_time as in_time, shifts.sft_name from attendance
    join employee on (attendance.att_emp_id = employee.emp_id)
    join shifts on (employee.emp_sft_id = shifts.sft_id)
where att_dir = 'In' and
att_time <= (
    select sft_from from shifts
    where sft_id = (
        select emp_sft_id from employee
        where emp_id = 5
    )
) and
att_date between '2012-04-01' and DATEADD(MONTH,1,'2012-04-01') and
att_date not in(
    select hld_dt from holidays
)

But I need a list of all dates of a month and the data retrieved by this query should be put on that date's row. Please, help... I badly need it.

Upvotes: 2

Views: 9695

Answers (2)

Waqar
Waqar

Reputation: 226

You need to make a view for the report, view that contains, employee table, attendance table, salary or pay table if any, and your shifts table, make a view and call that view to the report, and make a function in the report viewer and give it a query to select from that view, please let me know if you need any help.

Upvotes: 0

Lee Tickett
Lee Tickett

Reputation: 6037

Take a look at Handling non existent values in sql query expression for ssrs chart

You need a table containing dates in order to make this work (or you can use a stored procedure / common table expression or another method of generating the dates on the fly).

Upvotes: 0

Related Questions