CONTINUES 3 DAYS ABSENT IN SSMS LOGIC

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)

CONTINUES 3 DAYS ABSENT IN SSMS LOGIC

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-12 : 02:23:40

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-12 : 07:43:36

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

There are a few different ways I have seen people attempt this type of problems. One of my favorites is using the row_number function like shown below. This query assumes that you have a row for each employee for each calendar day without any gaps (for weekends or holidays, for example).

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

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-12 : 09:02:02

thank u sunitbeck for ur logic

i had applied same logic column names are changed

i had changed query this way
WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY rtt_emp_staffid ORDER BY a.rtt_ATTENDaNCE_DATE)
- ROW_NUMBER() OVER (PARTITION BY rtt_emp_staffid,rtt_STATUS ORDER BY a.rtt_ATTENDaNCE_DATE) Grp

FROM ATT_RPT_CALENDAR a where RTT_STATUS='A'
)
SELECT RTT_EMP_STAFFID , MIN(rtt_ATTENDaNCE_DATE) FROMDATE,MAX(rtt_ATTENDaNCE_DATE) TODATE,RTT_STATUS
FROM cte
where RTT_EMP_STAFFID ='M1302'
AND RTT_ATTENDANCE_DATE BETWEEN '2012-02-10' AND '2012-03-10'
--AND RTT_STATUS='A'
GROUP BY RTT_EMP_STAFFID ,Grp,RTT_STATUS
HAVING COUNT(*) >= 3;

i goy this output

id mindate maxdate

10 2012-02-10 2012-03-10

but i want emp sequetially absent in 3 days

thnk u

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-12 : 09:19:33

You should remove the "WHERE RTT_STATUS = 'A'" from the inner query and put that in the outer query. If that does not get you the desired results, can you post the sample data you are using?

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-13 : 06:08:43

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-13 : 06:56:48

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

There must be something in the data or set up that you are not describing in full here. The code as I posted it should work IF you have data for every day. Also, in your query, you are using DISTINCT. Are there multiple rows for each combination of EMPID, ATTENDENCEDATE, and STATUS?

What is the output you are getting for this data after you moved the WHERE clause to the outer query?

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-13 : 07:04:32

select distinct tab.RTT_EMP_STAFFID ,
Fromdate = TAB.rtt_ATTENDANCE_DATE,
Todate = dateadd(day, 3, TAB.rtt_ATTENDANCE_DATE),
b.RTT_STATUS
from ATT_RPT_CALENDAR b inner JOIN ATTENDANCE_REGISTER A ON a.EMP_STAFFID=b.rtt_EMP_STAFFID and a.ATTENDANCE_DATE=b.RTT_ATTENDANCE_DATE
inner JOIN (
SELECT RTT_EMP_STAFFID,RTT_ATTENDANCE_DATE, MAX(RTT_CRT_ON) DATE1,EMP.EMP_FIRSTNAME, EMP_MIDDLENAME
,EMP_LASTNAME,EMP_DATEOFJOINING,EMP_MAILID,EMP_REPORTINGTO FROM ATT_RPT_CALENDAR
INNER JOIN ERM_EMPLOYEE_MASTER EMP ON EMP.EMP_STAFFID= ATT_RPT_CALENDAR.RTT_EMP_STAFFID
--and ATT_RPT_CALENDAR.RTT_EMP_STAFFID='M1402'
--and ATT_RPT_CALENDAR.RTT_ATTENDANCE_DATE>='2012-04-15' and ATT_RPT_CALENDAR.RTT_ATTENDANCE_DATE<='2012-05-15'

GROUP BY RTT_ATTENDANCE_DATE,RTT_EMP_STAFFID, EMP.EMP_FIRSTNAME,EMP_MIDDLENAME,
EMP_LASTNAME,EMP_DATEOFJOINING,EMP_MAILID,EMP_REPORTINGTO

--order by ATT_RPT_CALENDAR.RTT_ATTENDANCE_DATE desc
) TAB ON b.RTT_ATTENDANCE_DATE=tab.RTT_ATTENDANCE_DATE --AND TAB.DATE1 = B.RTT_CRT_ON
AND TAB.RTT_EMP_STAFFID=B.RTT_EMP_STAFFID
inner join dbo.ERM_EMPLOYEE_MASTER em on em.EMP_STAFFID=b.RTT_EMP_STAFFID
INNER JOIN ERM_LOCATION_MASTER G ON G.LOCATION_ID=em.EMP_LOCATION_ID
INNER JOIN ERM_OU_MASTER L ON em.OU_ID=L.OU_ID

WHERE
b.RTT_STATUS in ('A')
AND tab.RTT_EMP_STAFFID='A0028'
and b.RTT_ATTENDANCE_DATE between '2012-02-10' and '2012-03-10'
and B.RTT_EMP_STAFFID IN
(
select x.rtt_EMP_STAFFID
from ATT_RPT_CALENDAR x
where x.RTT_EMP_STAFFID = b.rtt_EMP_STAFFID
and x.RTT_ATTENDANCE_DATE >= b.rtt_ATTENDANCE_DATE
and x.rtt_ATTENDANCE_DATE <= dateadd(day, 3, b.rtt_ATTENDANCE_DATE)
--and x.RTT_STATUS = 'A'
-- --and x.RTT_EMP_STAFFID='M1402'
group by x.RTT_EMP_STAFFID
having count(*) >= 3 )
ORder by tab.RTT_ATTENDANCE_DATE desc

------I have commented inner where staus ='A'

OUTPUT OF THE QUERY
id fromdate todate status
A0028 2012-03-09 00:00:00.000 2012-03-12 00:00:00.000 A
A0028 2012-03-07 00:00:00.000 2012-03-10 00:00:00.000 A
A0028 2012-02-24 00:00:00.000 2012-02-27 00:00:00.000 A
A0028 2012-02-23 00:00:00.000 2012-02-26 00:00:00.000 A
A0028 2012-02-22 00:00:00.000 2012-02-25 00:00:00.000 A
A0028 2012-02-13 00:00:00.000 2012-02-16 00:00:00.000 A
A0028 2012-02-10 00:00:00.000 2012-02-13 00:00:00.000 A

expected out put is
id fromdate todate status
A0028 2012-02-24 00:00:00.000 2012-02-27 00:00:00.000 A
A0028 2012-02-23 00:00:00.000 2012-02-26 00:00:00.000 A
A0028 2012-02-22 00:00:00.000 2012-02-25 00:00:00.000 A

plese need the help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-13 : 07:04:33

This is a sample data that I put together using your description. It produces the output I am expecting it to produce.

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

BTW, if you post questions with scripts that can generate the test tables and populate with sample data (like what I have done here), usually you get faster and better answers.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-13 : 08:55:53

I posted my last reply before I saw your posting at 06/13/2012 : 07:04:32. I am not able to get any more insights based on that posting.

Take a look at the sample data and example I posted in my last reply and see if that helps at all?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources