Date different query

itkaushik · 2018-04-29 08:23:52 UTC

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

ahmeds08 · 2018-04-29 10:52:34 UTC

post your table structure with sample data.

itkaushik · 2018-04-30 02:56:16 UTC

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

ahmeds08 · 2018-04-30 06:33:39 UTC

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

itkaushik · 2018-04-30 07:57:24 UTC

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

this error show

itkaushik · 2018-04-30 08:14:59 UTC

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

bitsmed · 2018-04-30 15:54:13 UTC

Please show the fieldtypes of

attndate
signintime
signouttime

harishgg1 · 2018-11-26 14:15:22 UTC

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

mannesravya · 2018-11-26 18:18:04 UTC

Could you please provide the fields along with sample data.

harishgg1 · 2018-11-26 18:20:52 UTC

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