• I believe i have the code working now :

    but it just seems too long

    CREATE TABLE #temp

    (

    Row_Ndb INT ,

    Account_Nbr INT ,

    Account_Name CHAR(5) ,

    Account_Desc CHAR(3) ,

    Begin_Date DATE ,

    End_Date DATE

    )

    INSERT INTO #temp

    VALUES

    ( 1, 1, 'test1', 'chk', '1/1/2012', '3/1/2012' ),

    ( 2, 1, 'test1', 'chk', '3/2/2012', '6/30/2012' ),

    ( 3, 1, 'test1', 'chk', '7/11/2012', '7/22/2012' ),

    ( 3, 1, 'test1', 'chk', '8/1/2012', '10/11/2012' ),

    ( 4, 1, 'test1', 'chk', '10/19/2012', '1/1/2013' );

    WITH cte

    AS ( SELECT

    row_ndb ,

    account_nbr ,

    account_name ,

    Account_desc ,

    Begin_date ,

    End_date ,

    ROW_NUMBER() OVER ( PARTITION BY Account_Nbr ORDER BY begin_date ) rownum

    FROM

    #temp

    ),

    cte_Middle

    AS ( SELECT DISTINCT

    *

    FROM

    ( SELECT

    cte1.* ,

    CASE WHEN DATEDIFF(d, cte2.End_date, cte1.begin_date) > 1

    THEN DATEDIFF(d, cte2.End_date, cte1.begin_date)

    ELSE 0

    END AS DaysMissing ,

    CASE WHEN DATEDIFF(d, cte2.End_date, cte1.begin_date) > 1

    THEN DATEADD(d, 1, cte2.end_date)

    ELSE NULL

    END AS MissingBegin_Date ,

    CASE WHEN DATEDIFF(d, cte2.End_date, cte1.begin_date) > 1

    THEN DATEADD(d, -1, cte1.Begin_date)

    ELSE NULL

    END AS MissingEnd_Date

    FROM

    cte cte1 --CASE Min_Begin_Date > '1/1/2012' < Min_End_Date THEN '1/1/2012' ELSE DATEADD(d, 1, cte2.end_date

    LEFT JOIN cte cte2

    ON cte1.account_nbr = cte2.account_nbr

    AND cte1.rownum = cte2.rownum + 1

    ) source

    WHERE

    source.DaysMissing > 0

    ),

    Cte_Top

    AS ( SELECT DISTINCT

    *

    FROM

    ( SELECT

    cte1.* ,

    CASE WHEN DATEDIFF(d, '1/1/2012', Min_Begin_Date) > 1

    THEN DATEDIFF(d, '1/1/2012', Min_Begin_Date)

    ELSE 0

    END AS DaysMissing ,

    CASE WHEN Min_Begin_Date > '1/1/2012'

    AND Min_Begin_Date < Min_End_Date

    THEN '1/1/2012'

    ELSE NULL

    END AS MissingBegin_Date ,

    CASE WHEN DATEDIFF(d, '1/1/2012', Min_Begin_Date) > 1

    THEN DATEADD(d, -1, Min_Begin_Date)

    ELSE NULL

    END AS MissingEnd_Date

    -- ,

    --Min_Begin_Date ,

    --Min_End_Date

    FROM

    cte cte1 --CASE Min_Begin_Date > '1/1/2012' < Min_End_Date THEN '1/1/2012' ELSE DATEADD(d, 1, cte2.end_date

    CROSS APPLY ( SELECT TOP 1

    ( Begin_Date ) Min_Begin_Date ,

    ( End_Date ) Min_End_Date ,

    account_Name

    FROM

    cte c3

    WHERE

    cte1.account_nbr = c3.account_nbr

    ORDER BY

    begin_date ASC

    ) cte3

    LEFT JOIN cte cte2

    ON cte1.account_nbr = cte2.account_nbr

    AND cte1.rownum = cte2.rownum + 1

    WHERE

    Min_End_Date = cte1.End_Date

    ) source

    ),

    Cte_Button

    AS ( SELECT

    *

    FROM

    ( SELECT

    cte1.* ,

    CASE WHEN DATEDIFF(d, Max_End_Date, '12/31/2012') > 1

    THEN DATEDIFF(d, Max_End_Date, '12/31/2012')

    ELSE 0

    END AS DaysMissing ,

    CASE WHEN DATEDIFF(d, Max_End_Date, '12/31/2012') > 1

    --AND Min_Begin_Date < Min_End_Date

    THEN DATEADD(d, 1, Max_End_Date)

    ELSE NULL

    END AS MissingBegin_Date ,

    CASE WHEN DATEDIFF(d, Max_End_Date, '12/31/2012') > 1

    THEN '12/31/2012'

    ELSE NULL

    END AS MissingEnd_Date

    -- ,

    --Min_Begin_Date ,

    --Min_End_Date

    FROM

    cte cte1 --CASE Min_Begin_Date > '1/1/2012' < Min_End_Date THEN '1/1/2012' ELSE DATEADD(d, 1, cte2.end_date

    CROSS APPLY ( SELECT TOP 1

    ( Begin_Date ) Max_Begin_Date ,

    ( End_Date ) Max_End_Date ,

    account_Name

    FROM

    cte c3

    WHERE

    cte1.account_nbr = c3.account_nbr

    ORDER BY

    begin_date DESC

    ) cte3

    LEFT JOIN cte cte2

    ON cte1.account_nbr = cte2.account_nbr

    AND cte1.rownum = cte2.rownum + 1

    WHERE

    Max_End_Date = cte1.End_Date

    ) source

    )

    SELECT

    *

    FROM

    cte_Middle

    WHERE DaysMissing > 0

    UNION

    SELECT

    *

    FROM

    Cte_Top

    where DaysMissing > 0

    UNION

    SELECT

    *

    FROM

    Cte_Button

    WHERE

    DaysMissing > 0

    DROP TABLE #temp