ATTENDENCE REPORT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

ATTENDENCE REPORT

Author

Topic

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-04 : 07:10:03

Dear All,
i have to develop a report HR Attendence report which will give me only those records where employee are absent from last 4 days
,days will be compare from current date.

plesae suggest
my table strucure is

empid name dt
M0382 Manisha Desai 5/3/2012 0:00
M0382 Manisha Desai 5/2/2012 0:00
M0382 Manisha Desai 4/25/2012 0:00
M0382 Manisha Desai 4/24/2012 0:00

regards,
Vipin jha

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-04 : 07:32:49

What does the data in the table represent? Are those dates on which the employee was absent? Also, what is the data type of the dt column?

Are you looking for only employees who were absent for 14 consecutive days, or for anyone who was absent at least on one day within the last 14 days?

Assuming the right data types etc., something like this:

SELECT * FROM AttendaceReport
WHERE dt > DATEADD(dd,-14,CAST(GETDATE() AS DATE));

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-04 : 07:39:06

no sir i ma not getting what i want

scenario is
suppose empid 1234 is absent from 30th apr to 4 may 2012
that means empid 1234 is absent from last 5 days
so he shoulkdcome in my output

i hope u understand

regards,
vipin jha

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-04 : 09:34:03

What does the data in the table represent? Are those dates on which the employee was absent, or are they dates on which the employee was present?

If those are the days on which the employee was absent, then the query I posted earlier should give you the results. (Unless the data type of the dt column is not a datetime type).

If the data in the table represent days on which the employee was present, would there be an entry even for holidays and weekends? If there would not be, how do you determine holidays? Are they only Saturdays and Sundays, or are there other holidays on weekdays?

If you can post representative sample input data with the expected output along with DDL for the tables, that would make it much easier for someone on the forum to post useful answers. If you need help with that, take a look at Brett's blog here:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-05-04 : 11:42:06

[CODE]-- Assumes a function named WorkingDate which finds the actual working date, relative to the current date, after adjusting for Holidays and Weekends, etc.

select empid, name
from Attendance
where empid not in (
select empid
from Attendance
where dt >= WorkingDate(-4) -- Last four days
group by empid
having count(*) = 4
)[/CODE]It's not clear if "the last four days" includes today or not. You may need to adjust the boundary values used.

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources