This isn't really all that complex, and we can use code that doesn't require any specific date setting(s). I used a CROSS APPLY just to get the date calc out of the main query.

select *, dbo.NumDOWBetween

(

DOW,

StartDate,

EndDate) AS Function_Result

,CASE WHEN startdate_DOW > enddate THEN 0 ELSE

1 + DATEDIFF(DAY, startdate_DOW, EndDate) / 7 END AS Query_Result

from ##TempMyDates

cross apply (

select DATEADD(DAY, -DATEDIFF(DAY, CASE DOW WHEN 'Mon' THEN 0 WHEN 'Tue' THEN 1 WHEN 'Wed' THEN 2

WHEN 'Thu' THEN 3 WHEN 'Fri' THEN 4 WHEN 'Sat' THEN 5 ELSE 6 END,

DATEADD(DAY, 6, StartDate))%7, DATEADD(DAY, 6, StartDate)) as startdate_DOW

) as find_first_dow_on_or_past_startdate

Edit: Put SQL code in a code block. Hopefully the names used explain the code, but if you need additional clarification, naturally please just say so.