Imo you could maybe start with a query like this.  The 'final_cte' CTE selects the row(s) with the final status for each Deal.  Then OUTER APPLY is used to count...

It's not a good logical model.  What is the maximum number of rows per Deal?  If it's 2 you could join the table to itself

I just ran into this same issue.  The short answer is to create a #temp table instead and it works (because the temp table exists within the same execution context...

The other comment.  Your comment was very on topic 🙂

Off topic much?  A couple is 2 and a few is 3 and more than a few is 5 but...

Well a lot could said about this topic.  The classic threaded forum/subforum organization might seem straightforward it depends on many things.  If by "threaded" it means "nested html" then that's...

Maybe you could use a CTE to make sure the right table source of the JOIN in the UPDATE statement contains appropriate unique priorityCode's

with unq_priority_cte(priorityid,priorityCode,priorityname) as (
...

It...

Ken McKelvey this looks familiar

https://www.sqlservercentral.com/forums/topic/need-to-create-gaps-in-a-table-of-date-ranges

Again one "brute force" way to do this would be to expand all the date ranges and use the set operator EXCEPT to remove the (also...

It's a one time only type query or it's intended to be run frequently?  The id value in the last row of the sample data should be 5, no?

O sorry, I was thinking you meant 2nd from the end.  I seemed to have missed the date range part too

declare @StartDate DATE = '2021-09-24', @EndDate DATE...

You could try using ROW_NUMBER twice.  Once to find the most recent row within the MyIdentifier partition and then again to find the 2nd oldest row (also within the MyIdentifier...

Call it what you want, using a GUID to "represent the PK" or vice versa makes no sense to me.   Both columns are fixed width (unless you've done row...

Maybe it was incorrect to label the GUID column 'guid_id' because it was intended as a public attribute of the primary key. Maybe it would be better labelled as 'securitystamp'...

Thank you Jeff.  On some projects we've had logical/physical GUID issues (or idk what to call it, let's say it's the subject of ongoing analysis) which we've sort of managed...