Hi,

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