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