find out the continuous date or single date using sql server

CREATE TABLE #Employee_Leave

(emp_id INTEGER NOT NULL,

 leave_date DATEtime NOT NULL

 

 PRIMARY KEY (emp_id, leave_date));

 

INSERT INTO #Employee_Leave(emp_id,leave_date)

 

select 47, '2012-11-29'

union all

select 47, '2012-11-30'

union all

select 47, '2012-12-04'

union all

select 47, '2012-12-05'

union all

select 47, '2012-12-07'

union all

select 47, '2012-12-10'

union all

select 48, '2012-11-29'

union all

select 48, '2012-11-30'

union all

select 48, '2012-12-04'

union all

select 48, '2012-12-05'

union all

select 48, '2012-12-07'

union all

select 48, '2012-12-10'

union all

select 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

 

 

Output

emp_id  StartDate                                   EndDate                                    Days      Type

47         2012-11-29 00:00:00.000           2012-11-30 00:00:00.000           2          Continuous

47         2012-12-04 00:00:00.000           2012-12-05 00:00:00.000           2          Continuous

47         2012-12-07 00:00:00.000           2012-12-10 00:00:00.000           4          Connecting

48         2012-10-14 00:00:00.000           2012-10-14 00:00:00.000           1          Single

48         2012-11-29 00:00:00.000           2012-11-30 00:00:00.000           2          Continuous

48         2012-12-04 00:00:00.000           2012-12-05 00:00:00.000           2          Continuous

48         2012-12-07 00:00:00.000           2012-12-10 00:00:00.000           4          Connecting