CODEWITHSUNDEEP
c#sqlwpfc#-4.0ms-access-2007
J R B
J R B

Reputation: 2136

Query for absent and present Employee

I have two tables Employee and Attendance, now i want date wise record both absent and present employee.

Like:--

Table1: Employee (EmpId,Name)

Table2: Attendance (Empid, CheckInOutDateTime, Status (like --I/O))

(Record will not available when Employee is Absent in Attendance Table but need record)

Now I want output:

**EmpID    Name  Date          Status**

1         Emp1   06/11/2013     Persant

2         Emp2   06/11/2013     Absant

3         Emp3   06/11/2013     Persant

1         Emp1   06/12/2013     Persant

2         Emp2   06/12/2013     absant

3         Emp3   06/12/2013     absant

Upvotes: 0

Views: 3376

Answers (5)

Amit Singh
Amit Singh

Reputation: 8109

Try like this...

Select Empid,EmpName,Date1,(
cASE WHEN EXISTS(
SELECT EMPID FROM Atten AT WHERE T.EMPID=AT.EMPID AND T.date1=AT.Chechout
) then 'Present' Else 'Absent' End )as Status
FROM
(
Select Empid,EmpName,Cast(Chechout as DATE)AS DATE1 from Emp a,(Select Distinct Chechout from Atten) b
) T

in ms access-:

Select Empid,EmpName,Date1,(iif((SELECT Count(Empid)FROM Atten AT WHERE T.EMPID=AT.EMPID AND T.date1=DateVAlue(AT.Chechout))>0,'Absent','Present')) as Status
FROM(
Select Empid,EmpName,DateVAlue(Chechout) AS DATE1 from Emp a,(Select Distinct DateValue(Chechout) as Chechout  from Atten) b
 )T

SQL Fiddle Demo

Upvotes: 1

Ganesh Jadhav
Ganesh Jadhav

Reputation: 2848

Here is the pseudo code:

for each date as varDate
   query = "SELECT COUNT(*) AS cnt FROM Employee te LEFT JOIN Attendance ta ON te.Empid = ta.Empid WHERE ta.CheckInOutDateTime = " + varDate.ToString() + " GROUP BY te.Empid";

While displaying the result of above query, you can do this:

if Convert.ToInt32(dataRow("cnt")) > 0
   status = "Present";
else
   status = "Absent";

Upvotes: 0

Ahmed
Ahmed

Reputation: 450

You can try this:

SELECT A.EmpId,A.Name,B.CheckInOutDateTime,status = CASE WHEN B.STATUS = 'IN' THEN 'PRESENT'
ELSE 'ABSENT' END from Table1 A join Table2 B ON A.Empid=B.empid

Upvotes: 0

Tom Collins
Tom Collins

Reputation: 4069

I could probably write a fancy query to get the results you want, but it would also require a 3rd table with every possible date.

I think it would be easier to have a scheduled task that added the absent records at the end of each day. Or you can make everyone absent ahead of time, then change the record to present as the employee shows up.

Upvotes: 0

nvoigt
nvoigt

Reputation: 77304

The SQL feature you are looking for is called an outer join. You may also want to read up on the other SQL features.

If for whatever reason you cannot use an outer join, you can use a subselect and count the records in the attendance table because you are only interested in a boolean value, either there or not there for a given day (count > 0, employee was present, count = 0, employee was absent).

Upvotes: 0

Related Questions