DECLARE @Shifts TABLE
    (staffid int, shiftdesc varchar(20), shiftdate datetime, shifthours float)

INSERT INTO @Shifts
SELECT 1, 'Sick', '2012-02-01', 8 
UNION ALL SELECT 1, 'Sick', '2012-02-02', 8
UNION ALL SELECT 1, 'Nothing', '2012-02-03', 8
UNION ALL SELECT 1, 'Sick', '2012-02-04', 8 
UNION ALL SELECT 1, 'Working', '2012-02-05', 8
UNION ALL SELECT 1, 'Working', '2012-02-06', 8 
UNION ALL SELECT 1, 'Sick', '2012-02-07', 8

SELECT staffid, periodstart=MIN(shiftdate), periodend=MAX(shiftdate)
    ,shiftdesc=MAX(shiftdesc)
    ,shifthours=SUM(shifthours) 
FROM (
    SELECT staffid, shiftdesc, shiftdate, shifthours
        ,n=shiftdate-ROW_NUMBER() OVER (
            PARTITION BY staffid, 
                CASE shiftdesc WHEN 'Working' THEN 0 ELSE 1 END 
            ORDER BY shiftdate)
    FROM @Shifts) a
GROUP BY staffid, n
ORDER BY staffid, periodstart

DECLARE @Shifts TABLE
 (staffid int, shiftdesc varchar(20), shiftdate datetime, shifthours float)

INSERT INTO @Shifts
          SELECT 1, 'Sick',    '2012-02-01', 8 
UNION ALL SELECT 1, 'SickOff', '2012-02-02', 8
UNION ALL SELECT 1, 'Sick',    '2012-02-02', 8 -- Two shifts in a day
UNION ALL SELECT 1, 'Sick',    '2012-02-03', 8
UNION ALL SELECT 1, 'SickOff', '2012-02-04', 8

UNION ALL SELECT 1, 'Working', '2012-02-06', 8 

UNION ALL SELECT 1, 'Sick',    '2012-02-07', 8

UNION ALL SELECT 2, 'Sick', '2012-02-04', 8 
UNION ALL SELECT 3, 'Working', '2012-02-05', 8

SELECT 
	staffid, 
	periodstart = MIN(shiftdate), 
	periodend = MAX(shiftdate),
	shiftdesc = MAX(shiftdesc),
	shifthours = SUM(shifthours) 
FROM (
	SELECT staffid, shiftdesc, shiftdate, shifthours, x.Working,
		n = shiftdate - 
			DENSE_RANK() OVER (PARTITION BY staffid ORDER BY shiftdate, x.Working)
	FROM @Shifts
	CROSS APPLY (SELECT Working = CASE shiftdesc WHEN 'Working' THEN 1 ELSE 0 END) x
) a
GROUP BY staffid, n, Working
ORDER BY staffid, periodstart