SQL Server Query for employee wise monthly attendance report

Question

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.

I can query all the columns except the date. I cannot show a list of dates of a particular month with beside all other fields. — Raihan, Apr 09 '12 at 06:36
@Mikael in my attendance table, there is a date for attendance... the report will be based on that date, but I need to show all the dates of that month... not only the date present in the attendance table — Raihan, Apr 09 '12 at 06:48
Sorry, I did not notice the scroll bar. To get the absent dates you should have a table with all the dates you are interested in. That table can be persisted or generated on the fly. Then you do a outer join against the attendance table. — Mikael Eriksson, Apr 09 '12 at 06:52

score 0 · Answer 1 · edited May 23 '17 at 10:28

0

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).

edited May 23 '17 at 10:28

Community

1
1

answered Apr 09 '12 at 07:42

Lee Tickett

5,847
8
31
55

score 0 · Answer 2 · answered Jun 13 '13 at 06:53

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.

SQL Server Query for employee wise monthly attendance report

2 Answers2