Hi,,
I want to query continuously absent list more five day employee but it' how i don't know. some one help me please.
post your table structure with sample data.
my table name is .
attendance and field is Empid, Attndate, Signintime , Signouttime. now i calculation to which emp Id are continue absent more 5 day. but how can i do it..
can you try this
WITH absentList
AS
(
SELECT EmpID, Attndate FromDate,
LEAD(Attndate) OVER (PARTITION BY EmpID ORDER BY Attndate ASC) ToDate
FROM attendance
)
SELECT DISTINCT EmpID
FROM absentList
WHERE DATEDIFF(d, FromDate ,ToDate) >= 5
Msg 195, Level 15, State 10, Line 2
'LEAD' is not a recognized built-in function name.
this error show
sql 2008 R2 using............
Please show the fieldtypes of
- attndate
- signintime
- signouttime
hi kaushik
i know this topic is 7 months ago
i am practicing my SQL
doing time pass also
i have two solutions
one using join
two using recursive cte
Join Solution
drop create data
use tempdb
go
drop table attendance
go
create table attendance
(
Empid int ,
Attndate date,
Signintime time,
Signouttime time
)
go
insert into attendance select 1,'2018-10-16','15:12:54:00',' 22:12:54:00'
insert into attendance select 1,'2018-10-17','11:12:54:00',' 22:12:54:00'
insert into attendance select 1,'2018-10-18','09:12:54:00',' 11:12:54:00'
insert into attendance select 1,'2018-10-26','11:12:54:00',' 17:12:54:00'
insert into attendance select 2,'2018-10-16','15:12:54:00',' 22:12:54:00'
insert into attendance select 2,'2018-10-26','11:12:54:00',' 22:12:54:00'
insert into attendance select 2,'2018-10-27','09:12:54:00',' 11:12:54:00'
insert into attendance select 2,'2018-10-28','11:12:54:00',' 17:12:54:00'
insert into attendance select 3,'2018-10-16','15:12:54:00',' 22:12:54:00'
insert into attendance select 3,'2018-10-17','11:12:54:00',' 22:12:54:00'
insert into attendance select 3,'2018-10-18','09:12:54:00',' 11:12:54:00'
insert into attendance select 3,'2018-10-19','11:12:54:00',' 17:12:54:00'
insert into attendance select 4,'2018-10-25','15:12:54:00',' 22:12:54:00'
insert into attendance select 4,'2018-10-26','11:12:54:00',' 22:12:54:00'
insert into attendance select 4,'2018-10-27','09:12:54:00',' 11:12:54:00'
insert into attendance select 4,'2018-10-28','11:12:54:00',' 17:12:54:00'
go
SQL
; WITH cte
AS (SELECT Row_number()
OVER(
partition BY empid
ORDER BY attndate) AS rn,
*
FROM attendance)
SELECT b.empid,
Datediff(day, b.attndate, a.attndate)
FROM cte a
JOIN cte b
ON a.empid = b.empid
AND a.rn = b.rn + 1
WHERE Datediff(day, b.attndate, a.attndate) > 5
go
Result
Recursive CTE solution
Recursive CTE SQL
; WITH cte
AS (SELECT Row_number()
OVER(
ORDER BY empid, attndate) AS rn,
*
FROM attendance),
recursive_cte
AS (SELECT *,
NULL AS daysabsent
FROM cte
WHERE rn = 1
UNION ALL
SELECT a.*,
CASE
WHEN a.empid = b.empid THEN
Datediff(day, b.attndate, a.attndate)
ELSE 0
END AS daysabsent
FROM cte a
JOIN recursive_cte b
ON a.rn = b.rn + 1)
SELECT empid,
daysabsent
FROM recursive_cte
WHERE daysabsent > 5
go
Result
Could you please provide the fields along with sample data.
drop create data
use tempdb
go
drop table attendance
go
create table attendance
(
Empid int ,
Attndate date,
Signintime time,
Signouttime time
)
go
insert into attendance select 1,'2018-10-16','15:12:54:00',' 22:12:54:00'
insert into attendance select 1,'2018-10-17','11:12:54:00',' 22:12:54:00'
insert into attendance select 1,'2018-10-18','09:12:54:00',' 11:12:54:00'
insert into attendance select 1,'2018-10-26','11:12:54:00',' 17:12:54:00'
insert into attendance select 2,'2018-10-16','15:12:54:00',' 22:12:54:00'
insert into attendance select 2,'2018-10-26','11:12:54:00',' 22:12:54:00'
insert into attendance select 2,'2018-10-27','09:12:54:00',' 11:12:54:00'
insert into attendance select 2,'2018-10-28','11:12:54:00',' 17:12:54:00'
insert into attendance select 3,'2018-10-16','15:12:54:00',' 22:12:54:00'
insert into attendance select 3,'2018-10-17','11:12:54:00',' 22:12:54:00'
insert into attendance select 3,'2018-10-18','09:12:54:00',' 11:12:54:00'
insert into attendance select 3,'2018-10-19','11:12:54:00',' 17:12:54:00'
insert into attendance select 4,'2018-10-25','15:12:54:00',' 22:12:54:00'
insert into attendance select 4,'2018-10-26','11:12:54:00',' 22:12:54:00'
insert into attendance select 4,'2018-10-27','09:12:54:00',' 11:12:54:00'
insert into attendance select 4,'2018-10-28','11:12:54:00',' 17:12:54:00'
go