Min effective and termdate for contiguous dates

Question

Hi,

Is there w way to achieve following using an SQL 2000 query, i looked everywhere but could not find any working snippet.

I have contiguous date segments and requirement is to get the min effective date and maximum effective dates for each contiguous dates.

if that is not possible getting min effective date and max termdate for an contiguous segment using different queries will also work for me.

ID  effdate  termdate
1   2007-05-01 2007-05-31
2   2007-06-01 2007-06-30
3   2007-07-01 2007-09-30
4   2008-03-01 2008-03-31
5   2008-05-01 2008-05-31
6   2008-06-01 2008-06-30

Expected Result :

2007-05-01  2007-09-30
2008-03-01  2008-03-31
2008-05-01  2008-06-30

Answer 1

Unfortunately you're probably going to have to use a cursor. Something like this should work.

DECLARE @Results TABLE
(
    effdate DATETIME,
    termdate DATETIME
)

DECLARE @Date1 DATETIME, 
     @Date2 DATETIME,
     @StartDate DATETIME, 
     @EndDate DATETIME

DECLARE @Cursor CURSOR 

SET @Cursor = CURSOR FAST_FORWARD
FOR
SELECT effdate, termdate FROM <TABLE>

OPEN @Cursor
FETCH NEXT FROM @Cursor
INTO @Date1,@Date2

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @StartDate IS NULL
    BEGIN
     SELECT @StartDate = @Date1,
       @EndDate = @Date2
    END
    ELSE
    BEGIN
     IF DateDiff(d,@EndDate,@Date1) = 1
     BEGIN
      SET @EndDate = @Date2
     END
     ELSE
     BEGIN
      INSERT INTO @Results
      SELECT @StartDate, @EndDate

      SELECT @StartDate = @Date1,
        @EndDate = @Date2
     END
    END


    FETCH NEXT FROM @Cursor
    INTO @Date1,@Date2
END

INSERT INTO @Results
SELECT @StartDate, @EndDate

CLOSE @Cursor
DEALLOCATE @Cursor 

SELECT * FROM @Results

Answer 2

select min(effdate),max(termdate) from tab1 where year(effdate) = year(termdate) group by year(effdate) order by min(effdate)

Result

2007-05-01 00:00:00.000 2007-09-30 00:00:00.000

2008-03-01 00:00:00.000 2008-06-30 00:00:00.000

Answer 3

I don't think this is possible without the use of a temporary table. (You don't exclude their use, but you didn't exclude using cursors either until Mike Bennett had posted his answer)

I'm reasonably confident that this is a generic solution - it uses an undocumented feature where it's possible to change the value of a variable more than once during an update statement.

You may be able to skip the creation of an artificial identity column to guarantee order (autoID in my query) if the records in your table are entered in order of effdate.

-- Setup test data

IF object_id('tempdb..#test1') IS NOT NULL
        DROP TABLE #test1
GO

CREATE TABLE #test1
(id INT
,effdate DATETIME
,termdate DATETIME
)

INSERT #test1
      SELECT 1,'2007-05-01','2007-05-31'
UNION SELECT 2 ,'2007-06-01','2007-06-30'
UNION SELECT 3 ,'2007-07-01','2007-09-30'
UNION SELECT 4 ,'2008-03-01','2008-03-31'
UNION SELECT 5 ,'2008-05-01','2008-05-31'
UNION SELECT 6 ,'2008-06-01','2008-06-30'
GO

IF object_id('tempdb..#t') IS NOT NULL
        DROP TABLE #t

GO

-- Order the records by effdate
SELECT IDENTITY(INT,1,1) AS autoId
,cast(NULL AS INT) groupID
,*
INTO #t
FROM #test1
ORDER BY effdate

UPDATE #t
SET groupID = 1
WHERE autoID = 1

DECLARE @gp INT
SET @gp = 1

--update groupID using the undocumented variable-update method
UPDATE t2
SET @gp = CASE WHEN t1.termdate = t2.effdate - 1
               THEN @gp 
               ELSE @gp + 1
          END
,groupID = @gp
FROM #t AS t1
JOIN #t AS t2
ON t1.autoID = t2.autoID - 1

--output results
select min(effdate), max(termdate)
from #t
group by groupID
order by groupID

Answer 4

I did something like this to get the effdate and same for termdate, made them as two separate views and got the final result.

SELECT distinct e0.effdate,e0.ID
  FROM  dbo.datatable e0    LEFT OUTER JOIN dbo.datatable PREV ON       
       PREV.ID = e0.ID 
 AND  PREV.termdate = DATEADD(dy, -1, e0.Effdate)        
  WHERE PREV.ID IS NULL

Answer 5

Celko has a solution to this starting on page 648 of SQL For Smarties, Third Edition