StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
538202
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2009-02-11T18:45:05.307
FavoriteCount
0
LastActivityDate
2009-02-11T18:45:05.307
LastEditDate
LastEditorUserId
0
OwnerUserId
63623
ParentId
533485
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
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. <pre><code>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 </code></pre> Results: <pre><code>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 </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POHow can I return a list of records based on contiguous time assignments in SQL Server 2000
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USTequila Jinx
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.