>DECLARE @DateBegin datetime, @DateEnd datetime >SET @DateBegin = '20060101' >SET @DateEnd = '20070719' > >CREATE TABLE #Trans (Fund char(3), TransDate datetime) >INSERT INTO #Trans VALUES ('ABC','20060101') >INSERT INTO #Trans VALUES ('ABC','20070602') >INSERT INTO #Trans VALUES ('ABC','20070603') >INSERT INTO #Trans VALUES ('ABC','20070615') > >INSERT INTO #Trans VALUES ('DEF','20070705') >INSERT INTO #Trans VALUES ('DEF','20070706') >INSERT INTO #Trans VALUES ('DEF','20070708') >INSERT INTO #Trans VALUES ('DEF','20070711') > >SELECT ISNULL(t1.Fund, t2.Fund) AS Fund, > DATEADD(dd,1, ISNULL(t1.TransDate, @DateBegin-1)) AS bd, > DATEADD(dd, -1, ISNULL(t2.TransDate, @DateEnd+1)) AS ed > FROM #trans t1 > FULL JOIN #trans t2 > ON t2.Fund = t1.Fund AND t2.TransDate > t1.TransDate > AND t1.TransDate BETWEEN @DateBegin AND @DateEnd > AND t2.TransDate BETWEEN @DateBegin AND @DateEnd > WHERE DATEDIFF(dd, ISNULL(t1.TransDate, @DateBegin-1), ISNULL(t2.TransDate, @DateEnd+1)) > 1 > AND NOT EXISTS ( SELECT * FROM #Trans > WHERE Fund = ISNULL(t1.Fund, t2.Fund) > AND TransDate > ISNULL(t1.TransDate, @DateBegin-1) > AND TransDate < ISNULL(t2.TransDate, @DateEnd+1)) >ORDER BY 1, 2 > >DROP TABLE #Trans >>
>>fund trans_date >>ABC 10/14/06 >>ABC 3/14/07 >>DEF 1/4/07 >>>>