Help finding 'pairs' of rows

[csuriano]

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

[mafaisal]

Hello

I think U Have got Result is Like
1 O C 3/1/2008 3/10/2008

Bcoz U have Getting Min & Max Date is Taken By group By of ID
Here ID is only 1

So Change ur Iserted Data to

INSERT #Data Values(1, '0', '3/1/2008')
INSERT #Data Values(1, 'C', '3/3/2008')
INSERT #Data Values(2, '0', '3/8/2008')
INSERT #Data Values(2, 'C', '3/9/2008')
INSERT #Data Values(2, 'C', '3/10/2008')
 

Then U Have Got 2 rows of Result

Like

1 O C 3/1/2008 3/3/2008
2 O C 3/8/2008 3/10/2008

Lets Try

Faisal

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

[csuriano]

That doesn't work. I only posted a subset of the data - the data for one ID. The real table has over 100,000 rows for 22,000 different IDs. And there may be multiple entries for each status as in the example below where there ar 2 Cs for the second O. I need to transform the original table into a second table.

Original Table looks like
ID Status UpdateDate
1 O 2008-03-01 00:00:00
1 C 2008-03-03 00:00:00
1 O 2008-03-08 00:00:00
1 C 2008-03-09 00:00:00
1 C 2008-03-10 00:00:00

New Table looks like
ID StartDate EndDate
1 2008-03-01 00:00:00 2008-03-03 00:00:00
1 2008-03-08 00:00:00 2008-03-10 00:00:00

The dates are in US format.

I have also simplified the data I'm looking at and what I need to produce
down to the bare essentials for this post. There's a lot more to the problem
I'm really trying to solve, but once I know how to do this part, I think I can figure
out the rest on my own.

This has to run on both SQL 2000 and 2005.

Thanks for your help.

Candi

[ck9663]

That doesn't work. I only posted a subset of the data - the data for one ID. The real table has over 100,000 rows for 22,000 different IDs. And there may be multiple entries for each status as in the example below where there ar 2 Cs for the second O. I need to transform the original table into a second table.

Original Table looks like
ID Status UpdateDate
1 O 2008-03-01 00:00:00
1 C 2008-03-03 00:00:00
1 O 2008-03-08 00:00:00
1 C 2008-03-09 00:00:00
1 C 2008-03-10 00:00:00

New Table looks like
ID StartDate EndDate
1 2008-03-01 00:00:00 2008-03-03 00:00:00
1 2008-03-08 00:00:00 2008-03-10 00:00:00

The dates are in US format.

I have also simplified the data I'm looking at and what I need to produce
down to the bare essentials for this post. There's a lot more to the problem
I'm really trying to solve, but once I know how to do this part, I think I can figure
out the rest on my own.

This has to run on both SQL 2000 and 2005.

Thanks for your help.

Candi

Your second row says:

1 2008-03-08 00:00:00 2008-03-10 00:00:00

Isn't the close date be 2008-03-09 00:00:00 ? If not, then the 2008-03-09 00:00:00 would be an orphan record. A close record with no paired Open record.

-- CK