455,377 Members | 1,354 Online
Need help? Post your question and get tips & solutions from a community of 455,377 IT Pros & Developers. It's quick & easy.

# Help finding 'pairs' of rows

 P: 2 I have a table with ID, Status and Date. The status is either Open or Close. For each ID there are multiple records. And there can be multiple entries with the same status but different times. I have to calculate the amount of time that the ID is in an open state. In the example below I have to match row 1 with row 2 and and row 3 with row 5. In other words, I want the min of the open and the max of the closed that is less than the next open. [code] CREATE TABLE #Data( ID int, Status char(1), UpdateDate datetime) INSERT #Data Values(1, '0', '3/1/2008') INSERT #Data Values(1, 'C', '3/3/2008') INSERT #Data Values(1, '0', '3/8/2008') INSERT #Data Values(1, 'C', '3/9/2008') INSERT #Data Values(1, 'C', '3/10/2008') [code] This is a far as I get. [code] SELECT r1.ID, r1.status AS StartStatus, r2.status AS EndStatus, r1.UpdateDate AS StartOfWork, r2.UpdateDate AS EndOfWork, DateDiff(day,r1.UpdateDate, r2.UpdateDate) as LengthOfWork FROM #data r1 JOIN #data r2 ON r1.ID = r2.ID Where r1.UpdateDate = (Select Min(r3.UpdateDate) FROM #data r3 WHERE r3.Status= 'O' AND r3.ID = r1.ID AND r3.UpdateDate <= r2.UpdateDate ) AND r2.UpdateDate = (Select Max(r4.UpdateDate) FROM #data r4 WHERE r4.rStatus ='C' and r4.ID = r2.ID AND r4.UpdateDate >= r1.UpdateDate ) [code] The results should be 1 O C 3/1/2008 3/3/2008 1 O C 3/8/2008 3/10/2008 I'm only getting one row. Any help would be greatly appreciated. Thanks, Candi Apr 5 '08 #1