Please provide sample data in the form of DDL to create (temp) tables and DML to insert data into those tables.

You do realize that UNION automatically removes duplicates.  Since your...

It's because you're using the wrong GROUP BY expression.  You're grouping on DATE, not by MONTH.  You need to use an expression that gives you the same value for every...

WHY are you using dynamic SQL for this?  WHY?

You never tell it to alter the view.  How is it supposed to know to alter the view unless you tell it...

FIRST, this will delete ALL records rather than "leaving a single item".

I would use ROW_NUMBER() rather than COUNT(), because ROW_NUMBER() guarantees a unique way to identify each row, whereas COUNT() does...

There are a couple of issues with your script.

• You don't actually insert the data into the table.
• You specify identity insert on, but you haven't included the identity, so...

I think the following will handle resetting the value to zero when the date changes and the direction changes.  I've added a calculation for the arrival date, since I'm using...

This is another approach that will also work across days.

WITH ua AS
(
    Select 
     [action] --This is where login and...

declare @torig table (custid int, employeecount int, datadate date)
insert into @torig values
(1, 10, '1-Jan-2023'), -- 21
(1, 11, '1-Jan-2023'),
(2, 11, '1-Jan-2023'),

(1, 15, '2-Jan-2023'), -- 28
(1, 11, '2-Jan-2023'),
(1,...

If you want tested code, please provide a script to create a sample data and expected results in the form of a script to create temp tables with insert scripts. ...

This is not an aswer to your question, but...

Your criteria AND (ITPRDC != 'AX' OR ITPRDC != ' ') looks wrong to me. And it's not your use of...

COUNT() with an OVER() clause does not support the DISTINCT keyword, so you'll have to do this in two steps.

WITH New_Pallet_Tags AS
(
    SELECT [PALLET_TAG#]
...

This produces the exact same results as your query.  One of the tricks to efficiently using CASE expressions is to remember that you do not have to test for conditions...

The reason that I calculated the LAG() twice is that I wanted different...

This requires reading the table TWICE.  You can do it by only reading the table ONCE.

WITH HorseRacesOrdered AS
(
    SELECT t1.HorseName, t1.LatestInfo, ROW_NUMBER() OVER(PARTITION BY...

Here is a different approach.

WITH cteUniquify AS
(
    SELECT *, CASE WHEN ph.Price = LAG(ph.Price) OVER(ORDER BY SomeDt) THEN 0 ELSE 1 END AS PriceChange
...