Similar to what Grant mentioned, I'd suggest creating a table to help manage the process. Run a process every day to update the table with the...

Similar to what Grant mentioned, I'd suggest creating a table to help manage the process. Run a process every day to update the table with the next day's...

Similar to what Grant mentioned, I'd suggest creating a table to help manage the process. Run a process every day to update the table with the next day's planned messages...

The dumb thing is not asking the question.

The second dumb thing (not applicable here!) is not listening to the answers given by seasoned pros, because you don't like what you...

Many people here won't open non-text attachments, so here is the code, reformatted courtesy of SQL Prompt:

USE Live_PTS;
GO

/****** Object:  StoredProcedure [dbo].[CMS_MakeUP_TIME_11-11-2020]    Script Date:...

Here's a version without requiring UNION:

SELECT tu.location_id
      ,tu.item_id
      ,tu.period
      ,tu.year_for_period
  ...

There's probably a more elegant way of doing this, but this seems to work (I fixed up your data types too)

DROP TABLE IF EXISTS #temp_usage;

CREATE TABLE #temp_usage
(
...

Phil, I don't understand why you think you need the self-join in your code.

It's simple, really: I never seem able to remember that you can update a CTE directly!

Building on Jeff's code:

--===== Create and populate the test able on-the-fly.
DROP TABLE IF EXISTS #TestTable --Makes reruns in SSMS easier.
;

SELECT *
INTO #TestTable
FROM
(
    VALUES
 ...

OK, so ID 3347 should have IDAll = 3 then, shouldn't it?

Given the ID 3347 value, can you confirm that the grouping should be on Name only?

And how do you determine the order in which the new IDAII values are assigned?

The only reason I can think of would be if there is other code which pertains only to the first condition – like this

IF @Var1 = @VarA
...

Try this instead

SELECT TOTAL_ACCT_NUM = COUNT(ACCT_ID) OVER (PARTITION BY ACCT_ID)
      ,ACCT_ID
      ,BUSINESS_NAME
     ...

Stepping back a little, have you tried importing your database into a Visual Studio database project and then attempting a build? That should throw out various errors and warnings to...

What do you mean by XML query? An XPATH query?