I wouldn't go thru all those gyrations to pull out the values.  Imagine the hassle to just add one more column to the output.

I'd do something similar to this instead.

I think this will work.  I have NOT yet adjusted it for performance (or tuned it at all yet, really), I was just trying to make it work.  Let me...

As noted, a LEFT JOIN is what you need.  An INNER JOIN requires a matching row in both tables; a LEFT JOIN will keep the row from the left-hand table...

You must use the alias to insure accurate UPDATEing.  You can, and quite often will, get inaccurate results when using the full table name instead.  (The error may now only...

I agree, sounds very much like class work.

Actually you're pulling more than 52 weeks' worth that way, but here goes.  Note that since you provided no sample data to test it with, you'll need to do the...

Excellent idea.  That gets much closer to the given size.

There's one very minor miscalc above, in that I believe page data can never exceed an absolute max of 8060 bytes,...

SELECT title, 
    SUBSTRING(title, CHARINDEX('E00', title), 200),
    Computed  = CASE WHEN CHARINDEX('E00', title) = 0 THEN NULL
   ...

Would you script out the table to make sure no other column "snuck" in there?

And please verify the row count:

SELECT SUM(rows) AS row_count
FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.SIData_222222_88_20211103_1436')...

Then follow the standard method and delete from the table in batches in clus key order.

In order to do that, you might first have to go thru the table and...

Well, if the datetime in the clus index is current datetime, you're ok.  Otherwise, not good, since you wouldn't be able to easily tell when copying the "good" rows that...

I got an email with just the initial q, I just quickly reviewed all the comments above, but my first thought was this:

;WITH ACTUALS AS
(
  SELECT...

Is the existing clustered composite key unique?  And explicitly declared to SQL as unique?

Yes, absolutely.  The test server has no idea what identity values are being assigned in prod and vice versa.

;WITH cte_dates AS (
    SELECT CAST(GETDATE() AS date) AS today,
        DATEPART(HOUR, GETDATE()) AS today_hour,
   ...