One more variation:

select #t.*
        , case when right(Disposition,nullif(charindex(space(1),reverse(disposition)),0)-1)
                    in ('CH', 'HC', 'MA', 'NH', 'OA', 'OTH', 'PSY', 'SNF')
                 then right(Disposition,nullif(charindex(space(1),reverse(disposition)),0)-1) end as ShortCode
into #r2

You were quicker, Drew.
---------------------------------------------------
-- helpful index
-- create unique nonclustered index IX1_EventStream on demoevents.EventStream(customer,id) include (eventname);

WITH cte1 as ( -- flag prior actions
                select id,...

First import your spreadsheet into a staging table in your database.    Then run a MERGE statement to add/update rows in your target table.    Both of these steps are well-documented.      

Since you apparently have a product limitation against full outer joins, you could try creating a view that does the full outer join for you and then report against the...

Nice read, Mike.   Short, clear, and concise.    Supported by execution plans and time/io stats.    This is what I like to see even though I've used lag() for years. ...

Good read, Steve.   Quick, clean, accurate, and on point.

One thing worth mentioning when using APPLY with a table valued function is the difference between CROSS APPLY and OUTER...

Looks tasty, if imperfect, to me, Steve.     Needs a big scoop of vanilla bean ice cream to go with. 🙂

Talk to me about perfection when we can compute pi exactly (in anything other than Base pi).    Reality is fuzzy 😉

Ears