quote:

---

Originally posted by kond.mohan

DEAR ALL

I HAVE TO CREATE A REPORT
I HAVE 2 TABLES
MY FIRST TABLE STRUCTRE

EMPID EMPNAME

MY SECOND TABLE STRUCTURE

EMPID ATTENDENCEDATE STATUS
001 2008/06/10 P
001 2008/06/11 P

001 2008/06/12 A
001 2008/06/13 A
001 2008/06/14 A
001 2008/06/15 A

001 2008/06/16 H
001 2008/06/17 pL
001 2008/06/18 W

I HAVE TO FIND THE EMPLOYEE WHO IS CONTINIOULY ABSENT IN 3 WORKING DAYS

---

;WITH cte AS
(
   SELECT   *,
      ROW_NUMBER() OVER (PARTITION BY EMPID ORDER BY ATTENDENCEDATE)
      - ROW_NUMBER() OVER (PARTITION BY EMPID,STATUS ORDER BY ATTENDENCEDATE) Grp
   FROM    YourTable
)
SELECT    EMPID, MIN(ATTENDENCEDATE),MAX(ATTENDENCEDATE)
FROM      cte
GROUP BY  EMPID,Grp
HAVING    COUNT(*) >= 3;

quote:

---

Originally posted by kond.mohan

-----
select distinct * from ATT_RPT_CALENDAR where RTT_EMP_STAFFID ='A0028'
and RTT_STATUS ='A'
and RTT_ATTENDANCE_DATE between '2012-02-10' and '2012-02-28'
order by RTT_ATTENDANCE_DATE desc

I GOT THIS OUT PUT
SAMPLE DATA OF MY QUERY


EMPID ATTENDENCE DATE STATUS
A0028 2012-02-24 00:00:00.000 A
A0028 2012-02-23 00:00:00.000 A
A0028 2012-02-22 00:00:00.000 A
A0028 2012-02-16 00:00:00.000 A
A0028 2012-02-15 00:00:00.000 A
A0028 2012-02-14 00:00:00.000 A
A0028 2012-02-13 00:00:00.000 A
A0028 2012-02-10 00:00:00.000 A

---

-- Table for test data
create table #tmp (EMPID VARCHAR(10),  ATTENDENCEDATE DATE, STATUS VARCHAR(2));

-- Data from OP showing dates on which status = 'A'
INSERT INTO #tmp VALUES 
('A0028','2012-02-24','A'),
('A0028','2012-02-23','A'),
('A0028','2012-02-22','A'),
('A0028','2012-02-16','A'),
('A0028','2012-02-15','A'),
('A0028','2012-02-14','A'),
('A0028','2012-02-13','A'),
('A0028','2012-02-10','A');

-- Fill in missing dates with data, with status = 'P'
;WITH cte AS (
	SELECT CAST('2012-02-10' AS date) AS Dt
	UNION ALL SELECT DATEADD(dd,1,dt) FROM cte WHERE dt < '2012-02-28'
)
INSERT INTO #tmp
SELECT 'A0028',Dt,'P' FROM cte 
WHERE Dt NOT IN (SELECT ATTENDENCEDATE FROM #tmp);

-- Query
;WITH cte AS
(
   SELECT   *,
      ROW_NUMBER() OVER (PARTITION BY EMPID ORDER BY ATTENDENCEDATE)
      - ROW_NUMBER() OVER (PARTITION BY EMPID,STATUS ORDER BY ATTENDENCEDATE) Grp
   FROM    #tmp
)
SELECT    EMPID, MIN(ATTENDENCEDATE),MAX(ATTENDENCEDATE)
FROM      cte
WHERE     STATUS = 'A'
GROUP BY  EMPID,Grp
HAVING    COUNT(*) >= 3;

-- output
--  A0028	2012-02-13	2012-02-16
--  A0028	2012-02-22	2012-02-24

-- Cleanup
DROP TABLE #tmp;