Absent 3 contonous working days

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Absent 3 contonous working days

Author

Topic

vipinjha123
Starting Member

45 Posts

Posted - 2012-06-01 : 02:25:50

Dear All,
i want to capture only those employee who is absent for continously 3 working days.
i am posting a sample atterndnce report of 1 employee
where you will see ststus like
P-present
w-weekly off(sat and sunday)
H-National holiday
PL-paid leave
A-absent
in given post emp M0000 is absnt continous 3 working days
so this employee must come in my report output

EMPID Attendence Date Status
M0000 05/31/12 P
M0000 05/30/12 P
M0000 05/29/12 P
M0000 05/28/12 P
M0000 05/27/12 W
M0000 05/26/12 W*
M0000 05/26/12 W*
M0000 05/25/12 P
M0000 05/24/12 A
M0000 05/23/12 A
M0000 05/22/12 A
M0000 05/21/12 A
M0000 05/20/12 W
M0000 05/19/12 W
M0000 05/18/12 P
M0000 05/17/12 P
M0000 05/16/12 P
M0000 05/15/12 P
M0000 05/14/12 P
M0000 05/13/12 W
M0000 05/12/12 W
M0000 05/11/12 P
M0000 05/10/12 PL
M0000 05/09/12 P
M0000 05/08/12 P
M0000 05/07/12 P
M0000 05/06/12 W
M0000 05/05/12 W
M0000 05/04/12 P
M0000 05/03/12 P
M0000 05/02/12 P
M0000 05/01/12 H

regards
Vipin jha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-01 : 02:50:21

[code]
select a.EmpID, fr_date = a.Attendence_Date, to_date = dateadd(day, 3, Attendence_Date)
from Attendence a
where Status = 'A'
and exists
(
select x.EmpID
from Attendence x
where x.EmpID = a.EmpID
and x.Attendence_Date >= a.Attendence_Date
and x.Attendence_Date <= dateadd(day, 3, a.Attendence_Date)
and x.Status = 'A'
group by x.EmpID
having count(*) = 3
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-01 : 02:58:22

[quote]Originally posted by khtan

--Creating Table

Create Table Ex
(EMPID varchar(10),
 AttendenceDate Date,
 Status char(2) )


--Inserting Sample Data

Insert Into Ex
Select 'M0000', '05/31/12',	'P' 
Union ALL
Select 'M0000', '05/30/12',	'P'
Union ALL
Select 'M0000', '05/29/12',	'P' 
Union ALL
Select 'M0000', '05/28/12',	'P'
Union ALL
Select 'M0000', '05/27/12',	'W' 
Union ALL
Select 'M0000', '05/26/12',	'W*' 
Union ALL
Select 'M0000', '05/26/12',	'W*' 
Union ALL
Select 'M0000', '05/25/12',	'P' 
Union ALL
Select 'M0000', '05/24/12',	'A'
Union ALL
Select 'M0000', '05/23/12',	'A'
Union ALL
Select 'M0000', '05/22/12',	'A'
Union ALL
Select 'M0000', '05/21/12',	'A'
Union ALL
Select 'M0000', '05/20/12',	'W'
Union ALL
Select 'M0000', '05/19/12',	'W' 
Union ALL
Select 'M0000', '05/18/12',	'P' 
Union ALL
Select 'M0000', '05/17/12',	'P' 
Union ALL
Select 'M0000', '05/16/12',	'P' 
Union ALL
Select 'M0000', '05/15/12',	'P' 
Union ALL
Select 'M0000', '05/14/12',	'P' 
Union ALL
Select 'M0000', '05/13/12',	'W' 
Union ALL
Select 'M0000', '05/12/12',	'W' 
Union ALL
Select 'M0000', '05/11/12',	'P' 
Union ALL
Select 'M0000', '05/10/12',	'PL' 
Union ALL
Select 'M0000', '05/09/12',	'P'
Union ALL
Select 'M0000', '05/08/12',	'P' 
Union ALL
Select 'M0000', '05/07/12',	'P' 
Union ALL
Select 'M0000', '05/06/12',	'W' 
Union ALL
Select 'M0000', '05/05/12',	'W' 
Union ALL
Select 'M0000', '05/04/12',	'P' 
Union ALL
Select 'M0000', '05/03/12',	'P' 
Union ALL
Select 'M0000', '05/02/12',	'P' 
Union ALL
Select 'M0000', '05/01/12',	'H'


--Query For Your Requirement

;With CTE
As
(Select *, ROW_NUMBER() Over (Partition By Status Order By AttendenceDate) As rn From Ex Where Status = 'A')
Select a.EMPID,
(Case When DateDiff(DD, a.AttendenceDate , d.AttendenceDate) >= 3 Then 'Absent For More Than Three Days' Else 'Not absent For More Than Three Days' End)
As AttendenceStatus
From CTE As a
JOIN CTE As d ON a.EMPID = d.EMPID AND d.rn = (a.rn + 3)

N 28° 33' 11.93148"
E 77° 14' 33.66384"

vipinjha123
Starting Member

45 Posts

Posted - 2012-06-01 : 03:39:18

thanks khtan

as per your query i modified my query like belwo

select a.EMP_STAFFID ,a.trans_type, fr_date = a.ATTENDANCE_DATE, to_date = dateadd(day, 3, ATTENDANCE_DATE)
from ATTENDANCE_REGISTER a
where TRANS_TYPE = 'A'
and exists
(
select x.EMP_STAFFID
from ATTENDANCE_REGISTER x
where x.EMP_STAFFID = a.EMP_STAFFID
and x.ATTENDANCE_DATE >= a.ATTENDANCE_DATE
and x.ATTENDANCE_DATE <= dateadd(day, 3, a.ATTENDANCE_DATE)
and x.TRANS_TYPE = 'A'
group by x.EMP_STAFFID
having count(*) = 3
)

but i ma not getting any data

regards,
Vipin jha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-01 : 03:42:55

what does that query return ?

KH
[spoiler]Time is always against us[/spoiler]

vipinjha123
Starting Member

45 Posts

Posted - 2012-06-01 : 03:57:20

NO RECORDS COMING

vipinjha123
Starting Member

45 Posts

Posted - 2012-06-01 : 05:28:31

Dear Venu,
when i am removing trans_type='a'
that time i am getting all the recird axcept absent one

quote:
Originally posted by vipinjha123

thanks khtan

as per your query i modified my query like belwo

select a.EMP_STAFFID ,a.trans_type, fr_date = a.ATTENDANCE_DATE, to_date = dateadd(day, 3, ATTENDANCE_DATE)
from ATTENDANCE_REGISTER a
where TRANS_TYPE = 'A'
and exists
(
select x.EMP_STAFFID
from ATTENDANCE_REGISTER x
where x.EMP_STAFFID = a.EMP_STAFFID
and x.ATTENDANCE_DATE >= a.ATTENDANCE_DATE
and x.ATTENDANCE_DATE <= dateadd(day, 3, a.ATTENDANCE_DATE)
and x.TRANS_TYPE = 'A'
group by x.EMP_STAFFID
having count(*) = 3
)

but i ma not getting any data

regards,
Vipin jha

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-01 : 05:54:59

quote:
Originally posted by vipinjha123

Dear Venu,
when i am removing trans_type='a'
that time i am getting all the recird axcept absent one
quote:
Originally posted by vipinjha123

thanks khtan

as per your query i modified my query like belwo

select a.EMP_STAFFID ,a.trans_type, fr_date = a.ATTENDANCE_DATE, to_date = dateadd(day, 3, ATTENDANCE_DATE)
from ATTENDANCE_REGISTER a
where TRANS_TYPE = 'A'
and exists
(
select x.EMP_STAFFID
from ATTENDANCE_REGISTER x
where x.EMP_STAFFID = a.EMP_STAFFID
and x.ATTENDANCE_DATE >= a.ATTENDANCE_DATE
and x.ATTENDANCE_DATE <= dateadd(day, 3, a.ATTENDANCE_DATE)
and x.TRANS_TYPE = 'A'
group by x.EMP_STAFFID
having count(*) = 3
)

but i ma not getting any data

regards,
Vipin jha

Which method are you talkin about??.,...mine or khtan's?

N 28° 33' 11.93148"
E 77° 14' 33.66384"

SQL Padawan
Starting Member

4 Posts

Posted - 2012-06-01 : 13:47:41

What I came up with, can be slow so make u test.

--Creating Table
DECLARE @Ex table
(empid varchar(10),
attendencedate Date,
stat char(2) )

--Inserting Sample Data

Insert Into @Ex
Select 'M0000', '05/31/12', 'P'
Union ALL
Select 'M0000', '05/30/12', 'P'
Union ALL
Select 'M0000', '05/29/12', 'P'
Union ALL
Select 'M0000', '05/28/12', 'P'
Union ALL
Select 'M0000', '05/27/12', 'W'
Union ALL
Select 'M0000', '05/26/12', 'W*'
Union ALL
Select 'M0000', '05/26/12', 'W*'
Union ALL
Select 'M0000', '05/25/12', 'P'
Union ALL
Select 'M0000', '05/24/12', 'A'
Union ALL
Select 'M0000', '05/23/12', 'A'
Union ALL
Select 'M0000', '05/22/12', 'A'
Union ALL
Select 'M0000', '05/21/12', 'A'
Union ALL
Select 'M0000', '05/20/12', 'W'
Union ALL
Select 'M0000', '05/19/12', 'W'
Union ALL
Select 'M0000', '05/18/12', 'P'
Union ALL
Select 'M0000', '05/17/12', 'P'
Union ALL
Select 'M0000', '05/16/12', 'P'
Union ALL
Select 'M0000', '05/15/12', 'P'
Union ALL
Select 'M0000', '05/14/12', 'P'
Union ALL
Select 'M0000', '05/13/12', 'W'
Union ALL
Select 'M0000', '05/12/12', 'W'
Union ALL
Select 'M0000', '05/11/12', 'P'
Union ALL
Select 'M0000', '05/10/12', 'PL'
Union ALL
Select 'M0000', '05/09/12', 'P'
Union ALL
Select 'M0000', '05/08/12', 'P'
Union ALL
Select 'M0000', '05/07/12', 'P'
Union ALL
Select 'M0000', '05/06/12', 'W'
Union ALL
Select 'M0000', '05/05/12', 'W'
Union ALL
Select 'M0000', '05/04/12', 'P'
Union ALL
Select 'M0000', '05/03/12', 'P'
Union ALL
Select 'M0000', '05/02/12', 'P'
Union ALL
Select 'M0000', '05/01/12', 'H'

SELECT
t1.empid,
fromdate = t1.attendencedate,
todate = t2.attendencedate
FROM @ex t1 LEFT JOIN @ex t2 ON t1.empid = t2.empid AND t1.attendencedate < t2.attendencedate AND t2.stat = 'A'
WHERE t1.stat = 'A'
AND NOT EXISTS(
SELECT 1
FROM @ex t3
WHERE t1.empid = t2.empid
AND t3.attendencedate BETWEEN t1.attendencedate AND t2.attendencedate
AND t3.stat <> 'A'
)
AND (DATEDIFF(d,t1.attendencedate,t2.attendencedate) + 1) = 3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources