Returning Dates not in the SQL Server DB Table

Hi,

I have a employee attendance table, and Im trying to return the dates on which an employee is absent. There are no table entries for an employee who is absent on a particular day. So can anyone please help on how i can return the dates on which an employee is absent.

Any help will be greatly appreciated,

Thanks,

Najla.

We need to see the structure of your table and possibly some data and expected output to understand what you are asking.

|||

The attendance table has columns:

emp_id attend_time attend_date attend_status

As an example, if an employee ABC with emp_id = 40 marks his attendance, the current date and time is automatically recorded. Also attend_status is marked as 'IN'.

I want to check attendance of an employee from 01/11/2007 to 06/11/2007.Consider an employee who was absent on 2,4,5 of Nov. Only his attendance of dates 1,3,6 would have been entered. So I can easily return his dates by :

select attend_date where emp_id=40

But what i want to ask is how can i return his absent dates which havent been entered in the table?

It definitely requires some C# coding. Somehow if i can get hold of the data and pass it through a loop, and compare it with some calendar, then i maybe able to do it.

Any suggestions?

Thanks,

Najla

|||

You can do it in C# also by using some logic, but if you want to achieve this in SQL that can be done with the below approach.

You can do it by having a separate table for all working dates for your office. No matter any employee was present or absent an entry for one working day will be there in your table. Then you can use this table with Left Join to the employee attendance table and you will get both the present and absent dates for any employee in a single select. The structure of the table can be like:

create table WorkingDays( rowIdidentity ( 1 , 1 ) , workingDatedatetime , totalEmployeesPresentint , totalEmployeesAbsentint)

The total present and absent employees are the 2 extra fields in this table, which I suppose you can use to store the extra information for a working day. Now, You can populate these records each day just after the attendance work is finished and utilize this table for finding out any employee's status on a particular day. The employee status according to your above requirement can be found out using the below query:

select w.workingdate , a.emp_idfrom workingDays wleftjoin attendanceon a.attend_date = w.workingDateand a.empId = 40where w.workindDatebetween'01/11/2007'and'06/11/2007'

You'll get emp_id null for the working days on which the employee was absent.

|||

OK thanks Dhimant, i get the approach and i think it can be easily done, but can anyone give me another idea without having to create a new table?

What i was thinking was to use a dataset and then comparing the values 1 by 1 to an array containing the dates b/w 1/11/2007 and 6/11/2007.
Is the approach feasible? How can I get values from the dataset and compare with the ones in the array?

Thanks,

Najla

|||

SELECT DISTINCT attend_date

FROM MyTable

EXCEPT

SELECT attend_date

FROM MyTable

WHERE emp_id=40

or

SELECT DISTINCT attend_date

FROM MyTable t1

WHERE attend_date NOT EXISTS(SELECT attend_date FROM MyTable t2 WHERE t1.attend_date=t2.attend_date AND t2.emp_id=40)

These however, make the assumption that someone else attended that day. Any day that noone attended isn't caught.