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

CREATE TABLE #Employee_Leave
(emp_id INTEGER NOT NULL,
 leave_date DATE NOT NULL,
 NO_Of_Days float not null,
 PRIMARY KEY (emp_id, leave_date,NO_Of_Days));
 

INSERT INTO #Employee_Leave (Emp_id,Leave_Date,NO_Of_Days) VALUES

(89,'11/1/2012',0.5),
(89,'11/2/2012',1),
(89,'11/5/2012',1),
(89,'11/6/2012',1),
(89,'11/7/2012',1),
(89,'11/8/2012',1),
(89,'11/9/2012',0.5),
(89,'11/10/2012',1),
(89,'11/11/2012',1),
(89,'11/12/2012',1),
(89,'11/13/2012',1),
(89,'11/14/2012',1),
(88,'11/14/2012',1),
(88,'11/15/2012',1),
(88,'11/27/2012',0.5),
(87,'11/27/2012',0.5)

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

 output
----------
emp_id	StartDate	EndDate	Days	Type
87	2012-11-27	2012-11-27	1	Single
88	2012-11-14	2012-11-15	2	Continuous
88	2012-11-27	2012-11-27	1	Single
89	2012-11-01	2012-11-12	12	Connecting
89	2012-11-06	2012-11-14	9	Continuous

expected output
----------------
emp_id	StartDate	EndDate	Days	Type
87	2012-11-27	2012-11-27	1	Single
88	2012-11-14	2012-11-15	2	Continuous
88	2012-11-27	2012-11-27	1	Single
89	2012-11-01	2012-11-12	14	Connecting

CREATE TABLE #Employee_Leave
(emp_id INTEGER NOT NULL,
 leave_date DATE NOT NULL,
 NO_Of_Days float not null,
 PRIMARY KEY (emp_id, leave_date,NO_Of_Days));
 

INSERT INTO #Employee_Leave (Emp_id,Leave_Date,NO_Of_Days) VALUES

(89,'11/1/2012',0.5),
(89,'11/2/2012',1),
(89,'11/5/2012',1),
(89,'11/6/2012',1),
(89,'11/7/2012',1),
(89,'11/8/2012',1),
(89,'11/9/2012',0.5),
(89,'11/10/2012',1),
(89,'11/11/2012',1),
(89,'11/12/2012',1),
(89,'11/13/2012',1),
(89,'11/14/2012',1),
(88,'11/14/2012',1),
(88,'11/15/2012',1),
(88,'11/27/2012',0.5),
(87,'11/27/2012',0.5)

-- calculate leaves
-- Use Jeff Moden's approach to calculate islands of contiguous dates
-- http://www.sqlservercentral.com/articles/T-SQL/71550/
;WITH cteGroupedDates AS (
        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
    ),
    cteGroupedDates2 AS (
        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),
    -- Above will produce overlapping dates for longer periods so use Itzik Ben-Gan's approach
    -- to group the overlapping dates.
    -- http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx
    C1 AS (
        SELECT emp_id, ts, Type2, Type
            ,e=CASE Type2 WHEN 1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY emp_id, Type2 ORDER BY EndDate) END
            ,s=CASE Type2 WHEN -1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY emp_id, Type2 ORDER BY StartDate) END
        FROM cteGroupedDates2
        CROSS APPLY (
            VALUES (1, StartDate), (-1, EndDate)) a(Type2, ts)
        ),
    C2 AS (
        SELECT C1.*
            ,se=ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY ts, Type2 DESC)
        FROM C1),
    C3 AS (
        SELECT emp_id, ts, Type
            ,grpnm=FLOOR((ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY ts)-1) / 2 + 1)
        FROM C2
        WHERE COALESCE(s-(se-s)-1, (se-e)-e) = 0)
SELECT emp_id, StartDate=MIN(ts), EndDate=MAX(ts)
    ,Days=DATEDIFF(day, MIN(ts), DATEADD(day, 1, MAX(ts)))
    ,Type=MIN(Type)
FROM C3
GROUP BY emp_id, grpnm
ORDER BY emp_id,StartDate

DROP TABLE #Employee_Leave