Continuious Date Range

Question

Post reply

Continuious Date Range

hitesh-733762

SSC Rookie

Points: 43
More actions
June 15, 2012 at 7:34 am

#392534

Hi Friends, I need help!
I want to generate a report which will list out employees who are absent for 3 continuous days in a month. Pl Note that output should NOT list out emp who are absent for any 3 days in a month (i.e. dates not in sqeuence). It should only be 3 continuous days.
you can assume table EMP_ABSENT with 2 columns (empcode, absent_date)
example:
EMP_ABSENT('E1','01/Jan/2012')
EMP_ABSENT('E1','02/Jan/2012')
EMP_ABSENT('E1','03/Jan/2012')
EMP_ABSENT('E2','01/Jan/2012')
EMP_ABSENT('E2','05/Jan/2012')
EMP_ABSENT('E2','06/Jan/2012')
EMP_ABSENT('E3','25/Jan/2012')
EMP_ABSENT('E3','26/Jan/2012')
EMP_ABSENT('E3','27/Jan/2012')
So ouput should give me emp E1 and E3 but not E2.
Hitesh

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26984 More actions · Answer 1

WITH CTE AS (

SELECT empcode,absent_date,

ROW_NUMBER() OVER(PARTITION BY empcode ORDER BY absent_date) AS rn

FROM EMP_ABSENT)

SELECT empcode

FROM CTE

GROUP BY empcode,DATEADD(Day,-rn,absent_date)

HAVING COUNT(*)>=3

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

Mark-101232 (6/15/2012)
WITH CTE AS (
SELECT empcode,absent_date,
ROW_NUMBER() OVER(PARTITION BY empcode ORDER BY absent_date) AS rn
FROM EMP_ABSENT)
SELECT empcode
FROM CTE
GROUP BY empcode,DATEADD(Day,-rn,absent_date)
HAVING COUNT(*)>=3

Nicely done, Mark.

Hitesh,

The following article explains what Mark has done to solve this problem.

http://www.sqlservercentral.com/articles/T-SQL/71550/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hitesh-733762 SSC Rookie Points: 43 More actions · Answer 3

thanks for query BUT what do i do if i were to run this sql on SQL-2000. This report sql should be compatible for 2000 thru 2012.

Mark Cowne One Orange Chip Points: 26984 More actions · Answer 4

hitesh-733762 (6/20/2012)
thanks for query BUT what do i do if i were to run this sql on SQL-2000. This report sql should be compatible for 2000 thru 2012.

I don't have access to SQL Server 2000, but I think this should work. It won't perform well on large data sets.

SELECT empcode

FROM (SELECT t1.empcode,DATEADD(Day,-COUNT(*),t1.absent_date) AS rnDiff

FROM EMP_ABSENT t1

INNER JOIN EMP_ABSENT t2 ON t2.empcode=t1.empcode

AND t2.absent_date<=t1.absent_date

GROUP BY t1.empcode,t1.absent_date) X

GROUP BY empcode,rnDiff