CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
Almaji
Almaji

Reputation: 213

how to find employee who is absent continuously more than 10 days?

I have a table name Att. which has 2 columns: EmpID and Date. the date column contains all the dates an employee is present. i need write a query to find if any employee is continuously absent for more than 10 days.

EmpID | Date
  101 | 1/1/2012
  101 | 2/1/2012
  101 | 7/1/2012
  101 | 18/1/2012
  101 | 21/1/2012
  101 | 25/1/2012
  101 | 30/1/2012
  102 | 1/1/2012
  102 | 2/1/2012
  102 | 5/1/2012
  102 | 9/1/2012
  102 | 14/1/2012
  102 | 19/1/2012
  102 | 24/1/2012
  102 | 25/1/2012
  102 | 28/1/2012
  102 | 29/1/2012
  102 | 30/1/2012

the result should 101 here. how this can be done? please help.

Upvotes: 4

Views: 3957

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

One more option with OUTER APPLY and EXISTS

SELECT t1.[EmpID], t1.[Date], o.[EmpID], o.[Date],
       DATEDIFF(d, o.[Date], t1.[Date]) AS diff        
FROM TableName t1 
OUTER APPLY (
             SELECT t2.[EmpID], t2.[Date]
             FROM TableName t2
             WHERE EXISTS (
                           SELECT 1
                           FROM TableName t3
                           WHERE  t1.[Date] > t3.[Date] 
                             AND t1.EmpID = t2.EmpID
                           HAVING MAX(t3.[Date]) = t2.[Date]
                           ) 
             ) o
WHERE DATEDIFF(d, o.[Date], t1.[Date]) >= 10

Demo on SQLFiddle

Upvotes: 1

John Woo
John Woo

Reputation: 263723

if you are working with SQL Server 2012, you can use LEAD Analytical Function

WITH recordList
AS
(
  SELECT EmpID, Date FromDate,
         LEAD(Date) OVER (PARTITION BY EmpID ORDER BY Date ASC) ToDate
  FROM   tableName
)
SELECT DISTINCT EmpID
FROM recordList 
WHERE DATEDIFF(d, FromDate ,ToDate) >= 10

Other Link(s)

UPDATE 1

WITH firstList
AS
(
  SELECT EmpID, Date,
         ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Date ASC) RN
  FROM   tableName
)
SELECT  DISTINCT a.EmpID
FROM    firstList a
        INNER JOIN firstList b
          ON a.RN + 1 = b.RN
WHERE   DATEDIFF (d, a.Date , b.Date ) >= 10

Upvotes: 3

Patashu
Patashu

Reputation: 21773

You'd want to use a subquery. Something like

select * from Att a1
where day(a1.date)-10 > all
(select day(a2.date) from Att a2 where a2.empid = a1.empid and a2.date < a1.date)

The idea is that we select a row who's date, even if we subtract 10 days, is still greater than the date of all rows that occured earlier chronologically before it, for the same EmpID.

day(date) should be the function that returns the number of days that have occured since the epoch, NOT the day of the month or day of the week. Alternatively, you could do something like date-10*ticksinday. Whatever you do will depend on your SQL flavour :)

Once this makes sense to you, implement it.

Upvotes: 1

Related Questions