SQL Server Query for employee wise monthly attendance report

[Raihanorium]

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

[BitBlt]

What have you tried already?

Actually, I also wanted to say that in order to get any help at all, you need to supply a LOT more detail about your data structures, your data, and your expected result.

Example:
What are the datatypes of the columns?
What are the constraints and foreign keys? (in this example, these are pretty easy to guess, but I've seen databases where they weren't).
Do you care about Organizarion (I assume you actually mean 'Organization')? Where does it fit into the structure?
Do you care about Users? If so, where does it fit into the structure? If not, why did you include it?
Do you have to take holidays into account? If so, is that considered "Absent"?
What about "Leaves"? How are those handled? Is that considered "Absent"?
Are attendance rows pre-populated, then updated when an employee shows up for work?
What about weekends?
Does every day have every kind of shift?
What is the grouping of your report for totals? All employees? By Department? By Section? By Designation?

An so on, and so on. We aren't mind readers. We also won't just do your work for you (I'm being kind here, and assuming this isn't just some stupid homework assignment...is it?). Do some work, then we'll see what happens.

[Raihanorium]

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
)

[BitBlt]

Interesting. I tried to run your query and I got this:

Msg 207, Level 16, State 1, Line 14
Invalid column name 'hld_dt'.

Did you actually try to run your statement before you posted this? Do you understand what you're trying to produce? Do you understand how impossible it is to help you if you don't supply things like sample data, or boundary conditions, or correct names?

If you don't even put in enough effort to cobble together a statement that will execute, it's not worth our effort to help you. Would you like to try again?

[Raihanorium]

@BitBit,

I am sorry bro...

There must have some edits: the holidays table will be like:

holidays:
    hld_id
    thl_tp_id
    hld_dt
    hld_cmnt

And the table organizarion should be organization.

Finally my database should be like:

users: //the users of this application. not important in calculations

*usr_id
usr_fname
usr_lname
usr_uname
usr_pass

organization: //also not important in calculation

*org_id
org_name
org_address
org_tel
org_web
org_email
org_cat
org_desc

departments: //the highest level of the organization

*dpt_id(int, ai)
dpt_name(varchar)
dpt_desc

sections: //every department can have several sections

*sec_id(int, ai)
sec_dpt_id(int) //dpt_id of departments
sec_name(varchar)
sec_desc

designations: //every section can have some designation

*dsg_id(int, ai)
dsg_sec_id(int) //sec_id of sections
dsg_name
dsg_desc

employee: //every designation can have some employee

*emp_id(int, ai)
emp_fname(varchar)
emp_lname(varchar)
emp_phone
emp_email
emp_addr
emp_join_dt(date)
emp_salary
emp_card_no
emp_dsg_id(int) //designation of that employee. (dsg_id of designations)
emp_sft_id(int) //sft_id of shifts

shifts: //employees work in shifts

*sft_id(int, ai)
sft_name(varchar)
sft_from(time) //starting time
sft_to(time) //ending time
sft_desc

leaves: //employees may have leaves

*lev_id(int, ai)
lev_emp_id(int) //the employee who has taken a leave. (emp_id of employee)
lev_frm(date) //start date
lev_to(date) //end date
lev_desc

holidays:

*hld_id(int, ai)
hld_tp_id(int) //type of holiday. (hld_tp_id of holiday_types)
hld_dt(date)
hld_cmnt

holiday_types:

*hld_tp_id(int, ai)
hld_tp_name(varchar)
hld_tp_desc

attendance:

*att_id(int, ai)
att_emp_id(int) //emp_id of employee
att_time(time)
att_date(date)
att_dir(varchar) //diraction exactly 'In' or 'Out'

Please let me know if anything more is needed.