Looks like a gaps and islands problem.

DECLARE @t TABLE(PERSON CHAR(6), DT DATE, SHIFTTYPE VARCHAR(8))

INSERT INTO @t(PERSON, DT, SHIFTTYPE)

VALUES

('000001','20121001','WORKING'),

('000001','20121002','SICK'),

('000001','20121003','SICK'),

('000001','20121004','DAYOFF'),

('000001','20121005','SICK'),

('000001','20121006','WORKING'),

('000001','20121007','WORKING'),

('000001','20121008','SICK'),

('000001','20121009','SICK');

WITH CTE AS (

SELECT PERSON, DT, SHIFTTYPE,

ROW_NUMBER() OVER(PARTITION BY PERSON ORDER BY DT) AS rn1,

ROW_NUMBER() OVER(PARTITION BY PERSON, CASE WHEN SHIFTTYPE='WORKING' THEN 1 ELSE 0 END ORDER BY DT) AS rn2

FROM @t)

SELECT PERSON,

MIN(DT) AS FromDT,

MAX(DT) AS ToDT

FROM CTE

WHERE SHIFTTYPE<>'WORKING'

GROUP BY PERSON,rn1-rn2

ORDER BY PERSON,MIN(rn1);

____________________________________________________

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