Based on the examples i think you are looking to get the nearest saturday from the CT_Prom_End

If so you can try this or better still you can tell us how you are computing the column Promo_Week_End?


select a.*,b.*    
  from temp_willowton16Jan18 a
   join (select * from #cal where weekdayno=6)b
  on b.CalendarDate>=a.[ct_prom_start]
 --and b.CalendarDate<=a.[ct_prom_end] /* Changing this condition to the one below.*/
 and b.CalendarDate<=(case when 6- datepart(weekday,a.ct_prom_end) = 0 then /*If the end_date is on a saturday then allow 1 more week */
                                                   dateadd(day,7 ,a.ct_prom_end)
                                             else dateadd(day,6- datepart(weekday,ct_prom_end) ,ct_prom_end) /*Else find the nearest saturday from the end_date*/
                                    end)
--where IsWeekend = 1 and weekdayno=7 /*This condition was converting that left join to a JOIN in the original query*/