>>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 Thanks to all >> 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 >>>>>>