A Simple Select Question...

[Deam]

I need to combine two rows if the status code of the second row is Terminated:

PID StartDate StatusEndDate Status
1234 5/16/2001 ACTIVE
1234 1/14/2005 TERMINATED
5678 3/16/2002 ACTIVE
5678 7/6/2004 TERMINATED

In the aboove case I would like to create a single record so that it looks like:
PID StartDate StatusEndDate Status
1234 5/16/2001 1/14/2005 ACTIVE
5678 3/16/2002 7/6/2004 ACTIVE

Can anyone suggest the best way of doing this.

 

[vongrunt]

Shouldn't there be TERMINATED in last column?

And what if one PID has two or more StartDate/StatusEndDate intervals? Do you want 'em all or separately?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Deam]

You mean in the result set I want ? No I want it Active.

Good point... if someone has multiple records, like:
PID StartDate StatusEndDate Status
9876 8/30/1999 ACT
9876 1/28/2000 TERM
9876 2/11/2000 ACT
9876 3/13/2001 TERM
It should be:

PID StartDate StatusEndDate Status
9876 8/30/1999 1/28/2000 ACT
9876 2/11/2000 3/13/2001 ACT

 

[druer]

This is one of the many reasons not to rehire people, it makes IT's job more difficult.

 

[druer]

Is there any identity fields for the rows already, or is the data you've shown it?

 

[druer]

-- Create a temp table that holds the start dates
select PID, StartDate , NULL as StatusEndDate
into #myTempTable
from myRealTable
where Status = 'Active'

-- Set the status end date by using the start record that
-- is less than the term date and isn't already blank
Update #myTempTable
set #myTempTable.StatusEndDate = myRealTaable.StatusEndDate
from #myTempTable inner join myRealTable
ON #myTempTable.PID = myRealTable.PID
AND #myTempTable.StatusEndDate IS NULL
AND #myTempTable.StartDate <= myRealTable.StatusEndDate
WHERE Status = 'Terminated'

I'm sure this could be done without the temp table step but I'm just about on my way out the door for the day. Other approach would require some type of id instead of comparing start date < end date.