How to generate absentee report with date of absent

0
votes

I have a requirement to generate absentee report. Employees are allocated to specific date range and the daily punching IN/OUT recorded to CheckINOUT table.

CheckINOUT Table
EmployeeID-Int
CheckINOUT-smalldatetime

EmpAllocation Table
EmployeeID-Int
StartDate-smalldatetime
EndDate-smalldatetime

Report Format
EmployeeID | Date Absent

TIA.

3
sqlsql-server
There's nowhere near enough information in this question to enable someone to easily answer without performing a certain amount of guesswork. Assume that you are someone with no knowledge of the problem and read the question again and see if you feel it can be answered without any more information, or get a colleague to read it. If your colleague has questions about what you are trying to achieve, then so will the rest of us. – Tanner

3 Answers

1
votes

I am not sure that this meets your actual requirement but this may be a start for you..Try this(untested). 

SELECT * 
FROM   empallocation A 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   checkinout B 
                   WHERE  A.employeeid = B.employeeid 
                          AND b.checkinout BETWEEN a.startdate AND b.enddate)

I am adding this code based on the link you provided..

SQL FIDDLE DEMO

CREATE TABLE empalloc 
  ( 
     eid       INT, 
     startdate DATETIME, 
     endate    DATETIME 
  ) 

INSERT INTO empalloc 
VALUES     (001,'2014-10-01','2014-10-15'), 
           (002,'2014-10-10','2014-10-15') 

CREATE TABLE checkinout 
  ( eid     INT, 
    checkin DATETIME ) 

INSERT INTO checkinout 
VALUES     (001,'2014-10-03'),(001,'2014-10-04'),(001,'2014-10-08'),
           (001,'2014-10-09'),(001,'2014-10-10'),(001,'2014-10-11'),
           (001,'2014-10-13'),(001,'2014-10-15'),(002,'2014-10-12'),
           (002,'2014-10-13') 

;WITH cte 
     AS (SELECT eid, 
                startdate, 
                endate 
         FROM   empalloc 
         UNION ALL 
         SELECT eid, 
                Dateadd(dd, 1, startdate) startdate, 
                endate 
         FROM   cte 
         WHERE  startdate < endate) SELECT eid, 
       startdate 
FROM   cte 
WHERE  startdate >= '2014-10-01' 
       AND startdate < '2014-10-10' 
EXCEPT 
SELECT eid, 
       checkin 
FROM   checkinout

In where condition just add the date range in which u need to generate the report

0
votes
use `LEFT JOIN`

SELECT EA.EmployeeID , EA
FROM EmpAllocation EA
LEFT JOIN CheckINOUT C
on EA.EmployeeId = C.EmployeeID
AND C.CheckINOUT between EA.StartDate and EA.EndDate
WHERE C.checkINOUT is NULL
0
votes

This will list all absences for each employee.

;WITH DateRange AS
(
  SELECT EmployeeID, StartDate, EndDate FROM @EmpAllocation
  UNION ALL
  SELECT  EmployeeID, StartDate + 1, EndDate FROM DateRange WHERE StartDate < EndDate
)


select 
    d.EmployeeID, d.StartDate
from DateRange d
    left join @CheckINOUT c on c.EmployeeID = d.EmployeeID and c.CheckInOut = d.StartDate
where c.CheckInOut is null
order by d.EmployeeID, d.StartDate;