Here's another option....

I've listed all the fields so you can confirm it is working. You can decide which fields to use.

Regards,

Bevan

with cte

as

(

select ho.[WorkingDate], sl.[WorkDate] , sl.[BusinessUnit], sl.[ContactType], sl.[TotalContacts], sl.[SIH], sl.[SLEligible], sl.[InServiceLevel]

from #SLHIH as sl

inner join #HoursOfOperation as ho

on sl.WorkDate = ho.CalendarDate and sl.BusinessUnit = ho.BusinessUnit

)

select a.* , max_previous_work_date.maxDate, most_recent_previous_work_day.*

from cte as a

outer apply (select max(WorkDate) as maxDate from cte as b where b.WorkDate <= a.WorkDate and b.BusinessUnit = a.BusinessUnit and b.WorkingDate=1) as max_previous_work_date

outer apply (select * from cte as c where c.WorkDate = max_previous_work_date.maxDate and c.BusinessUnit = a.BusinessUnit) as most_recent_previous_work_day