Agreed.  Be nice if MS added the option to the CREATE TABLE statement, so that SQL would "know" that the variable length bytes are not needed at all for the...

I created a table with 433 columns: that's where I got the number from.  I was hoping that 500 might be possible but the variable-length byte overhead still being required killed...

One key thing to note is that you only need to calc the first Sunday date.  For future dates, you simply add 7 day intervals to that date.  For example:

Again, no method will allow you to have more than 433 nvarchar(max) values in one row.  Just use separate rows.  The entire design needs reworked to make it reasonable and...

Use an INSERT trigger to add the hashed column.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER employee_table__TR_INS
ON dbo.employee_table
AFTER INSERT
AS
SET NOCOUNT ON;
UPDATE et
SET row_value = HASHBYTES('SHA2_256', first_name + last_name)
FROM dbo.employee_table...

Forced-off-row types require only a 16-byte pointer.  The 2-byte variable-length overhead per pointer, though, causes even fewer than 500 columns to be possible [I thought SQL would be able to...

SELECT
    Yr, Pd, Loc,
    SUM(Complete) AS Complete,
    SUM(Pending) AS Pending,
    SUM(Canceled) AS Canceled,
  ...

If using nvarchar(max), in particular, you can simply force the large values off the page into LOB/overflow area.

After creating the table, but before loading it, run the following command:

EXEC sys.sp_tableoption...

Like so:

SELECT EmpName,
    MAX(CASE WHEN row_num = 1 THEN Item END) AS item1,
    MAX(CASE WHEN row_num = 2 THEN Item END)...

If I need to know the location, I usually set a variable with a location id that is unique.

DECLARE @code_location varchar(20)
SET @code_location = '1000-SELECT'
BEGIN TRY
...
END TRY
BEGIN CATCH
SELECT...

Just create a separate drive path for backups on that machine and, to keep it simple, make sure there are no spaces or other unexpected chars in the path name.

Btw,...

For an OUTER JOIN, you must put conditions on the possibly-missing table in the JOIN clause, not in the WHERE clause.

select * from #temp1 T

left join #temp2...

I don't know about the overall logic, but the JOINs above seem needlessly convoluted.  Instead, maybe:

  INNER JOIN #UserB AS B
    ON ...

I think the Milestone table does not contain a column named "TOT_ABSENCES".  Please review the column names in the Milestone table.

Create an index keyed on pbRecordId on that table.