To subtract the values the rows would need to be joined in some way.  Since maybe some Category(ies) is/are not always present for each Period you could try OUTER APPLY...

Like what Mr Brian Gale described you could try a combination of LEAD and LAG functions

with lead_lag_cte as (
    select *,
   ...

Interesting news.  Thank you.  Please reach out if there's anything I could do that might help.  Regarding the forum there was never a UI built so it's currently DDL, SQL...

That's really awesome Steve.  I know a lot of people are going to be happy you've picked up the ball with this and in this way too.  That's awesome.  It's...

It was very quick, and I knew about it yesterday, but didn't really do much other than give a vote to try. Still amazed it happened.

Comments, suggestions,...

What needs to be dynamic?

Maybe something like this

with part_cte as (
    select PartId,
           sum(case when CodeType=1885 then 1...

Yes it could be put in the same function

It's true...

That's the one I use.  There are others out there and if you're...

It seems the query is performing a partial cross join.  The cross join could be replaced by a tally function and window (rows between 30 preceding and current row)...

It seems safest to COUNT(DISTINCT CATEGORY) with GROUP BY PartId.  It's not clear if it's necessary to join back to the table to SELECT other CategoryIds.  If other CategoryIds (than...

Suppose there's a table of weekend workdays with "Makeup day after St. Patricks Day" in it.  The weekend workday(s) could be included in the query using OR EXISTS and a...

Suppose there's a table of holidays with St Patrick's Day in it.  The holiday(s) could be excluded in the query using WHERE NOT EXISTS and a correlated subquery.  Because St...

Yet another application where a tally function could be useful.  The projection is off by $1.11 perhaps due to rounding

declare
  @revMTD    ...