A trigger is actually perfect for this situation.  For the email, it's probably best to add row(s) to a table or queue, then construct and send the emails based on...

It seems like it, but SQL 2008 doesn't directly read JSON, afaik.  It might be easier to handle it with T-SQL rather than try to create some external function/process.

yyyy-mm-00 is not an interval either.  The interval would be yyyy-mm-01 to yyyy-mm-{EOM}.

yyyy-mm-00 sorta looks like a date, but it is not one, no matter how much fluff you put...

Limited sample data, but I think this should perform better, since it avoids all the CHARINDEX costs:

SELECT   A.ID, B.ItemNumber,
    MAX(CASE WHEN D.Item...

The disadvantage is that it violates 1NF big time.  A "date" such as yyyy-mm-00 is not a date it is multiple dates.  Thus, it violates 1NF.

Besides which, SQL Server does...

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...