finding absent for all employees

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

General SQL Server Forums

New to SQL Server Programming

finding absent for all employees

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-20 : 02:52:27

in my main table i have 100 employee attendance detail
can we write a query which will find absent for all the employees in one query

for example main table has following fields

empno entrydate timein timeout

1 1/1/2010 8 5
1 2/1/2010 8 5
1 3/1/2010 8 5
1 4/1/20105 8 5
2 1/1/2010 8 5
2 2/1/2010 8 5
2 3/1/2010 8 5
2 4/1/20105 8 5
3 1/1/2010 8 5
3 2/1/2010 8 5
3 3/1/2010 8 5
3 4/1/20105 8 5

how do we find absent for emp 1,2,3 in one query

Yalini2212
Starting Member

12 Posts

Posted - 2010-11-20 : 07:53:08

You can have the working day list in a table which contain dates. then using that you can check in this table using loop whether the employee has entered in this table.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 08:07:17

;with cte
as
(
select * from
(select distinct entrydate from tbl) a
cross join
(select ditince empno from tbl) b
)
select cte.empno, cte.entrydate
from cte
left join tbl e
on cte.empno = e.empno
and cte.entrydate = e.entrydate
where e.entrydate is null
order by cte.empno, cte.entrydate

The cte gives a list of possible empno, date combinations which it gets from the login table. If you have a table with the list of days and employees then use those instead.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-20 : 08:08:31

can u give me example

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources