Q:

SELECT NEXT ROW, COMPARE, SET PREVIOUS ROW

So I have a history table called Member_Phys_History in SQL-Server 2008
Looks like this:
RecID, MemberID, Phys_ID, Phys_Start, Phys_End, Phys_Update

The first column is identity, the Phys_Start, Phys_End, and Phys_Update are dates.
I have another table called Member_Phys_Update
MemberID, Phys_ID, Phys_Start_Date

So once a week or so this Update table get's an update from client where The Phys_ID changes and the Phys_start is later in time...So I add this information to my History table and it would look like this:
1|ABC123|555|2014-01-01|NULL|NULL
2|ABC123|556|2014-04-01|NULL|NULL

Here's what I need to do:
I want to basically set the first records Phys_End_Date to the day before the second records Phys_Start_Date. So it would look like this:
1|ABC123|555|2014-01-01|2014-03-30|NULL
2|ABC123|556|2014-04-01|NULL|NULL

I cannot use a stored procedure with a cursor unfortunately, my DBA says it's inefficient. I was wondering if there was any way I could do this in a couple queries...
A cursor may be ideal, but can I do this with a FETCH NEXT or something? 

A:

Try this
Select A.*, B.Phys_End_Date
from table1 A
outer apply (select (min(Phys_Start_Date) - 1) Phys_End_Date from table1 x
             where x.Phys_Start_Date > A.Phys_Start_Date
             AND X.MemberID = A.MemberID) B

SQL DEMO
Edit (Adding Update SQL)
update A
set A.Phys_End_Date = B.Phys_End_Date
from table1 A     
outer apply (select (min(Phys_Start_Date) - 1) Phys_End_Date from table1 x
             where x.Phys_Start_Date > A.Phys_Start_Date
             AND X.MemberID = A.MemberID) B

or

INSERT INTO table2 (memberid, phys_id,Phys_Start_Date,Phys_End_Date)
Select A.*, B.Phys_End_Date
from table1 A
outer apply (select (min(Phys_Start_Date) - 1) Phys_End_Date from table1 x
             where x.Phys_Start_Date > A.Phys_Start_Date
             AND X.MemberID = A.MemberID) B;

