SQL Calendar Table – Adding a WorkDayofMonth Column

In SQL Server you could use an updatable CTE, such as

alter table Calendar add WorkDayofMonth tinyint null;

with d as (
    select WorkDayofMonth,
      Row_Number() over(partition by CalendarMonth order by CalendarDate) n
    from Calendar
    where isWeekDay = 1 and isHoliday = 0
)
update d set WorkDayofMonth = n;

Obviously amending column names as appropriate for your table schema.