This can be achieved using CTE
declare @person table (PersonID int, ApptDate datetime, ApptStatus varchar(10), NoOfAppts int, OneStopOrNot VARCHAR(10))
insert into @person (PersonID, ApptDate, ApptStatus, NoOfAppts) values
(49820,'07-09-2013','Seen',1),
(49827,'12-sep-2013','Seen',1),
(49831,'07-sep-2013','Seen',1),
(49834,'07-sep-2013','Seen',1),
(50084,'07-sep-2013','Seen',1),
(50097,'05-sep-2013','Seen',1),
(50172,'05-sep-2013','Seen',1),
(50172,'27-sep-2013','Seen',2),
(50175,'05-sep-2013','Seen',1)
;WITH persons AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY PersonID, ApptStatus),
*
FROM @person
)
UPDATE cur SET OneStopOrNot =
( case WHEN cur.PersonID != prev.PersonID THEN 'OneStop'
WHEN prev.PersonID IS NULL THEN 'OneStop'
WHEN cur.PersonID = prev.PersonID AND prev.OneStopOrNot = 'MoreThan1' THEN 'MoreThan1'
WHEN cur.PersonID = prev.PersonID AND cur.ApptDate = prev.ApptDate THEN 'OneStop'
ELSE 'MoreThan1' END)
FROM persons cur
LEFT JOIN persons prev on prev.rownum = cur.rownum - 1
select * from @person