That being said, I think that this will work. Since there is no test data to test against, all the testing of this code is up to you.

UPDATE ME

SET Span_Num = CASE WHEN ME2.Eligibility_Id IS NULL THEN 1

WHEN ME2.Eligibility_Id = ME.Subscriber_Id THEN Span_Num + 1

ELSE 1

END

FROM dbo.Eam_Member_Eligibility ME

LEFT JOIN dbo.Eam_Member_Eligibility ME2

ON ME2.Subscriber_Id = ME.Subscriber_ID

AND ME2.EndDate = dateadd(d,-1,me.Start_Date)

ORDER BY Subscriber_Id, Start_Date

Edit: Note, I used a LEFT JOIN instead of just a JOIN. This is because the way you were using a subquery in the column list, you could return a null value for the rows that you are processing. If you want to just process yesterdays data, then replace the LEFT JOIN with JOIN.