By combining Mr. CELKO's politely supplied DDL and sample data, with the technique described by Jeff Moden in his excellent SQL Spackle article: Group Islands of Contiguous Dates[/url], I can come up with the following:

CREATE TABLE #Employee_Leave

(emp_id INTEGER NOT NULL,

leave_date DATE NOT NULL,

PRIMARY KEY (emp_id, leave_date));

INSERT INTO #Employee_Leave

VALUES

(47, '2012-11-29'), (47, '2012-11-30'), (47, '2012-12-04'), (47, '2012-12-05'),

(47, '2012-12-07'), (47, '2012-12-10'), (48, '2012-11-29'), (48, '2012-11-30'),

(48, '2012-12-04'), (48, '2012-12-05'), (48, '2012-12-07'), (48, '2012-12-10'),

(48, '2012-10-14');

WITH

cteGroupedDates AS

( --=== Find the unique dates and assign them to a group.

-- The group looks like a date but the date means nothing except that adjacent

-- dates will be a part of the same group.

SELECT emp_id,

UniqueDate = leave_date,

DateGroup = DATEADD(dd

,-ROW_NUMBER() OVER (

PARTITION BY emp_id ORDER BY emp_id,leave_date)

,CASE DATEPART(dw, leave_date) WHEN 2 THEN DATEADD(dd, -2, leave_date) ELSE leave_date END )

FROM #Employee_Leave

GROUP BY emp_id,leave_date

)

--===== Now, if we find the MIN and MAX date for each DateGroup, we'll have the

-- Start and End dates of each group of contiguous dates. While we're at it,

-- we can also figure out how many days are in each range of days.

SELECT emp_id,

StartDate = MIN(UniqueDate),

EndDate = MAX(UniqueDate),

[Days] = DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1,

[Type] = CASE WHEN DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1 = 1 THEN 'Single'

WHEN DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1 > 1 AND

DATEPART(dw, MIN(UniqueDate)) > DATEPART(dw, MAX(UniqueDate)) THEN 'Connecting'

ELSE 'Continuous' END

FROM cteGroupedDates

GROUP BY emp_id,DateGroup

ORDER BY emp_id,StartDate

DROP TABLE #Employee_Leave

Note that this solution is sensitive to the setting of DATEFIRST, i.e., it assumes the week starts on Sunday.