Date different query

Transact-SQL
1

Hi,,
I want to query continuously absent list more five day employee but it' how i don't know. some one help me please.

2

post your table structure with sample data.

3

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..

4

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

5

Msg 195, Level 15, State 10, Line 2
'LEAD' is not a recognized built-in function name.

this error show

6

sql 2008 R2 using............

7

Please show the fieldtypes of

  • attndate
  • signintime
  • signouttime
8

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

image

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

image

9

Could you please provide the fields along with sample data.

10
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