Try something set based like the following. The functions will need to be altered to take into account weekends, public holidays etc. Obviously this has not been tested.

create function dbo.PrevValidLeaveDate
(
 @LeaveDate...

Not quite sure what you are trying to do but this should produce the result you want:

select t1.fk_1, t1.fk_2, t1.[date]

from @tmpTable t1

where t1.[primary] = 'Y'

 and t1.[date] = (select max(t2.[date])

   from...

This should work in TSQL but is non-ANSI:

create function dbo.GetTable1IDs

(

 @T2ID int

)

returns varchar(4000)

begin

 declare @Result varchar(4000)

 set @Result = ''

 select @Result = @Result + cast(T1ID as varchar(10)) + ', '

 from dbo.table3

 where T2ID =...

select t1.fk_id_1, t1.fk_id_2, t1.[date]

from dbo. t1

where [primary] = 'Y'

 and t1.[date] = (select max(t2.[date])

   from dbo. t2

   where t1.fk_id_1 = t2.fk_id_1

    and t1.fk_id_2 = t2.fk_id_2)

Try:

insert into #TempNewDok (dDocName, dID, CMSdDocName)

select D.Did

 ,D.xDocName

from DocMeta D

 join Revisions R on D.Did = R.Did

 join  CMS.dbo.DocumentHistory dh on D.xDocRevisionID = dh.did

where D.Did = (select max(D1.Did)

  from DocMeta D1

  where D1.xDocName = D.xDocName)

 and R.dReleaseDate Between...