I finally had time to test this, and I found something I hadn't noticed before. Your test data (first post) has duplicate IDs and all the dates and times are the same. Is that an accident, or does your real data also have duplicate ID values? Also, the test table has no primary key. Is that true of the real table or just an oversight on the test?

In the hope that your data doesn't actually have those problems, I created the following test and code:

CREATE TABLE #mytable

( ID INT IDENTITY(1,1) ,

DateValue DATETIME, Prd nvarchar(250), Statusid INT )

INSERT INTO #mytable

(DateValue,Prd, Statusid)

SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL

SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL

SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL

SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL

SELECT 'Oct 17 2007 12:00AM','B','1' UNION ALL

SELECT 'Oct 17 2007 12:00AM','B','1' UNION ALL

SELECT 'Oct 17 2007 12:00AM','B','1' UNION ALL

SELECT 'Oct 17 2007 12:00AM','B','2' UNION ALL

SELECT 'Oct 17 2007 12:00AM','B','1' UNION ALL

SELECT 'Oct 17 2007 12:00AM','B','2' UNION ALL

SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL

SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL

SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL

SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL

SELECT 'Oct 17 2007 12:00AM','A','2'

update #MyTable

set DateValue = dateadd(minute, ID, DateValue)

alter table #MyTable

add SetFirst bit not null default(0), SetLast bit not null default(0)

;with CTE (Row, Date, Prd, StatusID, SetFirst, SetLast) as

(select row_number() over (partition by Prd order by DateValue),

DateValue, Prd, StatusID, SetFirst, SetLast

from #MyTable)

update C1

set

SetFirst =

case

when C2.row is not null then 1

when not exists

(select *

from #MyTable mt

where mt.prd = c1.prd

and mt.datevalue < c1.date) then 1

else 0

end,

SetLast =

case

when C3.row is not null then 1

when not exists

(select *

from #MyTable mt

where mt.prd = c1.prd

and mt.datevalue > c1.date) then 1

else 0

end

from CTE C1

left outer join CTE C2 -- The prior row is a 2

on C1.Prd = C2.Prd

and C1.Row = C2.Row+1

and C1.StatusID = 1

and C2.StatusID = 2

left outer join CTE C3 -- Status 2 and next row is a 1

on C1.Prd = C3.Prd

and C1.Row = C3.Row-1

and C1.StatusID = 2

and C3.StatusID = 1

select *

from #MyTable

Because of the Not Exists tests, it has at least that much row-by-row. There's almost certainly a better way to do that particular test, but I don't have time to mess around with that right now. This way works, at least on this data.

Another thing I noticed about your test data is that you had two StatusID 2 in a row for Prd "B". That seems to indicate a close without an open. I got rid of that for my test, but if it really exists in your database, the code will have to correct for that.