HR Attendence report in sql

Question

Dear All,
I am looking for a  sql query in which i can find the employee record who is absent from last 5 days continously.

my tabe structure is like

table name :-login
column :-empid,logindate

THANKX & regards,
Vipin jha

 

Answer 1

select *
from login
where not (logindate <getdate() and logindate>=getdate()-5);
go

or
select *
from login
where not (CONVERT(varchar,logindate,112) <CONVERT(varchar,getdate(),112) and logindate>=CONVERT(varchar,getdate()-5,112));

go

---

Many Thanks & Best Regards, Hua Min

Answer 2

no this logic is not correct

plesae modify and post again

thankx

Answer 3

may this help :

select * from 
login l1
where not exists(	select 1
					from login l2
					where l1.empID = l2.empid 
					AND logindate <=getdate() and logindate>=getdate()-5
)

---

Please vote if you find this posting was helpful or Mark it as answered.

Answer 4

Sorry, it is corrected

---

Many Thanks & Best Regards, Hua Min

Answer 5

does this help you?

select* from login
where logindate <getdate()-5

Or

select* from login
where CONVERT(varchar,logindate,102) <CONVERT(varchar,getdate()-5,102)

Regards
Satheesh

Answer 6

THIS LOGIC IS CORRECT BUT RECORS ARE REPEATING

MEANS  SUPPOSE EMIPD 111 IS ABSENT FROM 1ST MAY WHICH IS COMING CORRECT.

BUT ITS ALSO GIVING ALL THE PREVIOUS RECOED OF EMPID 111

I WANT TO SHOW ONLY RECORS ON THE BASIS OF 5 DAYS , UNIQUE RESULT

REGARDS,

VIPIN JHA

Answer 7

select distinct empid
from login
where not (CONVERT(varchar,logindate,112) <CONVERT(varchar,getdate(),112) and logindate>=CONVERT(varchar,getdate()-5,112));

go

---

Many Thanks & Best Regards, Hua Min

Answer 8

SELECT distinct
    empid ,max(Logindate)
FROM
    tablename
WHERE

datediff(dd,Logindate,getdate()) > 5
GROUP BY empid

---

Please vote if you find this posting was helpful or Mark it as answered.

Answer 9

Assuming you have a calendar table:

;with Dates as (select the_date 
from dbo.Calendar 
where the_date between cast(dateadd(day,-5, CURRENT_TIMESTAMP) as date) and CAST(CURRENT_TIMESTAMP as Date)),

Combinations as (Select D.the_Date, E.EmpID 
from Dates D cross join dbo.Employees E) -- all possible combinations of Employee logins for last 5 days:

select E.* from  Employees E where not exists (select 1

from Combinations C INNER JOIN Login L ON C.EmpID = L.EmpID  and C.the_Date = L.LoginDate

where C.EmpD = E.EmpID) -- this will select all employees who didn't log in in last 5 days at least once (so absent all 5 days)

For creation of the calendar table check 

Passing multiple ranges to stored procedure

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 10

This is simpler solution than mine and the correction is simple:

select distinct EmpID from login l1
where not exists(	select 1
					from login l2
					where l1.empID = l2.empid 
					AND logindate <=getdate() and logindate>=getdate()-5
)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog