How can I return a list of records based on contiguous time assignments in SQL Server 2000

Question

I have a Table that looks like this:

IP            Hostname   TransactionDate
------------- ---------- -------------------
1.1.1.1       A          2009-01-01 01:00:00
1.1.1.1       A          2009-01-02 01:00:00
1.1.1.1       A          2009-01-03 01:45:00
1.1.1.1       B          2009-01-04 01:00:00
1.1.1.1       A          2009-01-05 01:00:00

I would like to build a query to return records that will group results based on how long they've contiguously held an IP address:

ie (selecting or IP 1.1.1.1):

Hostname    GrantDate            ExpireDate
----------- ---------------      -----------------
A           2009-01-01 01:00:00  2009-01-04 01:00:00
B           2009-01-04 01:00:00  2009-01-05 01:00:00
A           2009-01-05 01:00:00  NULL

What's the best way to accomplish this task? I'd like to avoid cursors where possible. I'm using SQL Server 2000 which makes this more difficult...

Answer 1

There are a few solutions to this problem in Joe Celko's SQL Puzzles and Answers. Google has an excerpt which includes them. His examples include start and end times, but it should be fairly simple to adapt them.

Answer 2

Posted for Posterity's sake. I just can't find a way to do this without using a cursor. Part of it, I'm sure, is that SQL is not my strong point. If anyone else runs across this post, here's the solution I've used. I'm going to keep plugging away at using a query for it and will post that if I ever figure it out.

SET NOCOUNT ON
DECLARE @ip VARCHAR(15)
    SET @ip = '1.1.1.1'

DECLARE @dhcplog TABLE(IP VARCHAR(15), HOSTNAME VARCHAR(32), IPDATE DATETIME)
DECLARE @results TABLE(IP VARCHAR(15), HOSTNAME VARCHAR(32), STARTDATE DATETIME, ENDDATE DATETIME)
INSERT INTO @dhcplog VALUES('1.1.1.1', 'A', '2009-01-01 01:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'A', '2009-01-02 02:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'A', '2009-01-05 03:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'B', '2009-01-07 04:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'B', '2009-01-07 10:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'B', '2009-01-08 05:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'C', '2009-01-09 06:00:00')
INSERT INTO @dhcplog VALUES('1.1.1.1', 'A', '2009-01-10 07:00:00')

DECLARE @cHOST VARCHAR(32)
DECLARE @cEND DATETIME

DECLARE @tIP VARCHAR(15)
DECLARE @tHOST VARCHAR(32)
DECLARE @tIPDATE DATETIME

DECLARE IPCursor CURSOR FOR SELECT IP, HOSTNAME, IPDATE
          FROM @dhcplog
          WHERE IP = @ip
          GROUP BY IP, HOSTNAME, IPDATE
          ORDER BY IPDATE DESC

OPEN IPCursor
    FETCH NEXT FROM IPCursor INTO @tIP, @tHOST, @tIPDATE
     WHILE @@FETCH_STATUS = 0
      BEGIN
       IF @tHOST = @cHOST
        BEGIN
         UPDATE @results
         SET STARTDATE = @tIPDATE
         WHERE HOSTNAME = @cHOST
         AND ENDDATE = @cEND
        END
       ELSE
        BEGIN
         INSERT INTO @results (IP, HOSTNAME, STARTDATE, ENDDATE)
         VALUES (@tIP, @tHOST, @tIPDATE, @tIPDATE)
         SET @cHOST = @tHOST
         SET @cEND = @tIPDATE
        END
       FETCH NEXT FROM IPCursor INTO @tIP, @tHOST, @tIPDATE
      END
CLOSE IPCursor
DEALLOCATE IPCursor

SELECT * FROM @results

SET NOCOUNT OFF

Results:

IP   Hostname StartDate  EndDate
1.1.1.1 A  2009-01-10 07:00:00 2009-01-10 07:00:00
1.1.1.1 C  2009-01-09 06:00:00 2009-01-09 06:00:00
1.1.1.1 B  2009-01-07 04:00:00 2009-01-08 05:00:00
1.1.1.1 A  2009-01-01 01:00:00 2009-01-05 03:00:00